PostgreSQL problems on entity_id of type varchar with flagging and flag_count tables

I think I have the same problem. I get this error message:
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: bigint = character varying LINE 8: ...ON taxonomy_term_field_data_node__field_taxon.tid = flagging... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid, paragraphs_item_field_data_node__field_gyujtohely.id AS paragraphs_item_field_data_node__field_gyujtohely_id, taxonomy_term_field_data_node__field_taxon.tid AS taxonomy_term_field_data_node__field_taxon_tid, flagging_taxonomy_term_field_data.id AS flagging_taxonomy_term_field_data_id FROM {node_field_data} node_field_data LEFT JOIN {node__field_gyujtohely} node__field_gyujtohely ON node_field_data.nid = node__field_gyujtohely.entity_id AND (node__field_gyujtohely.deleted = :views_join_condition_0 AND node__field_gyujtohely.langcode = node_field_data.langcode) INNER JOIN {paragraphs_item_field_data} paragraphs_item_field_data_node__field_gyujtohely ON node__field_gyujtohely.field_gyujtohely_target_revision_id = paragraphs_item_field_data_node__field_gyujtohely.revision_id LEFT JOIN {node__field_taxon} node__field_taxon ON node_field_data.nid = node__field_taxon.entity_id AND (node__field_taxon.deleted = :views_join_condition_2 AND node__field_taxon.langcode = node_field_data.langcode) INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_taxon ON node__field_taxon.field_taxon_target_id = taxonomy_term_field_data_node__field_taxon.tid LEFT JOIN {flagging} flagging_taxonomy_term_field_data ON taxonomy_term_field_data_node__field_taxon.tid = flagging_taxonomy_term_field_data.entity_id AND flagging_taxonomy_term_field_data.flag_id = :views_join_condition_4 WHERE (node_field_data.status = :db_condition_placeholder_5) AND (node_field_data.type IN (:db_condition_placeholder_6)) ORDER BY node_field_data_created DESC NULLS LAST LIMIT 101 OFFSET 0; Array ( [:db_condition_placeholder_5] => 1 [:db_condition_placeholder_6] => gyujtes_node [:views_join_condition_0] => 0 [:views_join_condition_2] => 0 [:views_join_condition_4] => vedett )

I think the problem that is flagging_taxonomy_term_field_data.entity_id is varchar and taxonomy_term_field_data_node__field_taxon.tid is bigint type and Postgresql 8.3 stop to automatically cast bigint to varchar or back.

The query I can run on Postgres side:

SELECT node_field_data.created AS node_field_data_created,
    node_field_data.nid,
    paragraphs_item_field_data_node__field_gyujtohely.id AS paragraphs_item_field_data_node__field_gyujtohely_id,
    taxonomy_term_field_data_node__field_taxon.tid AS taxonomy_term_field_data_node__field_taxon_tid,
    flagging_taxonomy_term_field_data.id AS flagging_taxonomy_term_field_data_id
   FROM node_field_data node_field_data
     LEFT JOIN node__field_gyujtohely node__field_gyujtohely ON node_field_data.nid = node__field_gyujtohely.entity_id AND node__field_gyujtohely.deleted = 0 AND node__field_gyujtohely.langcode::text = node_field_data.langcode::text
     JOIN paragraphs_item_field_data paragraphs_item_field_data_node__field_gyujtohely ON node__field_gyujtohely.field_gyujtohely_target_revision_id = paragraphs_item_field_data_node__field_gyujtohely.revision_id
     LEFT JOIN node__field_taxon node__field_taxon ON node_field_data.nid = node__field_taxon.entity_id AND node__field_taxon.deleted = 2 AND node__field_taxon.langcode::text = node_field_data.langcode::text
     JOIN taxonomy_term_field_data taxonomy_term_field_data_node__field_taxon ON node__field_taxon.field_taxon_target_id = taxonomy_term_field_data_node__field_taxon.tid
     LEFT JOIN flagging flagging_taxonomy_term_field_data ON taxonomy_term_field_data_node__field_taxon.tid::text = flagging_taxonomy_term_field_data.entity_id::text AND flagging_taxonomy_term_field_data.flag_id::text = 'vedett'::text
  WHERE node_field_data.status = 5 AND node_field_data.type::text = 'gyujtes_node'::text
  ORDER BY node_field_data.created DESC NULLS LAST
 OFFSET 0
 LIMIT 1001;

I read the issue about DER integration. Maybe DER is the solusion?

Yup, sounds like this will probably be taken care of by #2678756: Allow config entities to be flagged.

(BTW, please do not add random tags to issues!)

Just tested even latest dev version, and I confirm bug, too.

PostgreSQL seems like an appropriate tag - hope you don't mind me restoring it @joachim.

Found the real problem here:
src/Entitity/Flagging.php:121

    $fields['entity_id'] = BaseFieldDefinition::create('string')
      ->setLabel(t('Entity ID'))
      ->setRequired(TRUE)
      ->setDescription(t('The Entity ID.'));

creates the entit_id as a string Base field definition, which is ok for MySQL-ish type of DBs which casts the '235' entity_id into unsigned int 235 as real entity id. But not PostgreSQL which is strict as school teacher of Math !!!

Bu basically it's not Postreses fault ... the definition of this base field shoul look like this:

    $fields['entity_id'] = BaseFieldDefinition::create('integer')
      ->setLabel(t('Entity ID'))
      ->setRequired(TRUE)
      ->setSettings([
        'unsigned' => true,
        'size' => 'big',
      ])
      ->setDescription(t('The Entity ID.'));

i think...
therefore...
i am...

PATCH included.
Love & Peace

PS: The Test suites need to be updated accordingly to work with this patch.

The problem now is that ADDING a new entity that is flagged now can't be done - cuz Flag tries to ensure it's policies on the $entity that is NEW and still has NO entity_id set ... so the part the SQL that tries the table flagging with entity_id = '' FAILS !!!

Patch in rework now ...
'll be there soon :)

Found the problem ... The configuration entity type of the module => "Flag" (not the content "Flagging") has a method:

/**
   * {@inheritdoc}
   */
  public function isFlagged(EntityInterface $entity, AccountInterface $account = NULL, $session_id = NULL) {
    \Drupal::service('flag')->populateFlaggerDefaults($account, $session_id);

    // Load the is flagged list from the flagging storage, check if this flag
    // is in the list.
    $flag_ids = \Drupal::entityTypeManager()->getStorage('flagging')
      ->loadIsFlagged($entity, $account, $session_id);
    return isset($flag_ids[$this->id()]);
  }

it doesn't controls the Entity existance but throws it into the storage selection directly, which leads into the error i've described in comment above.
So i tweaked the FlaggingStorage.php file's method loadIsFlagged to contain a control on entity id existance (simple $entity->isNew())

There you go - now it should work perfectly.

Cheers :)

Fixed comments on patch

Another patch that contains some other fixes of Postgres related problems.

- flag hooks on EVERY entity form or actions to be like (try install a theme and then uninstall it, the default block scheme installed upon layout will be tried to UNINSTALL, but the blocks configs are still ENTITIES, and try to uninstall it with modified entity_id to be big int now, on postgres, while it's trying to uninstall some config entity with id lik bartik_page_title block will give an SQL error where Postgres wipps you !

Still have to update the Testes on this patch, cuz they don't contain the modifications needed.
As for working status, this will work with flag-8.x-4.0-alpha3

Cheers.

Reworked some things in this patch and altered HOOK_shema that bears entity_id on flag_count table also varchar => transformed into bigint.
Then fixed some tests (removed 1 ajax test, placed comments around as it was'n working at all even on MySQL)

Cheers

Changing the title of the issue to be more appropriate.
It's not the view relationship, but all possible references on entity_id of flagging and flag_count tables which are made as varchar by default and the Type-Strict-ness of the PostreSQL that are in conflict which are fixed with the patches below.

Adding also the patch that works with release version 8.x-4.0-alpha3
I can't test it as there's no test choice for 4.0-alpha3 tagged version.

No, the patch in #14 is the wrong way to fix this, and the statements in #8 through #16 are wrong. (BTW, the patch in #16 is also no good because all patches must be applied to HEAD, not to some fixed-point release.)

In Drupal 8, entity IDs are strings. Period. This is true of core, and is true of the the Flag module. Changing them and redefining them to be integers is just wrong.

Read all the comments in #2929733: SQL query error on PostgreSQL 9.5 when try to list Bookmarks for details. That issue and this ought to be consolidated into one issue, but if you do that please don't just close one of the issues and lose all of the important comments from that issue.

@TR ok ... seems legit .
I've read now all the comments from the other issue.

It's harder to fix this in core, than in flag module imho, and my problem for now is that a production lvl project uses d8 + flags over Pgsql ... and it MUST work some-f**kin-how ... being said that:

I'm the first one to try to do things in a standard / best-practise ways. I do fully support your worry about the wrong way to address the problem here. But as for now - i have no other solutions (or budget to spend on this issue with client). As far as this is contained and version tracked i'll keep it this way.

But,
as i've already said ... it's harder to push a change or a patch into D core. It's as you've said on a sql driver level somewhere, and to apply something on this depth of core will take some effort. (probably the most elegant and clean solution here)

Another way is to contain it within flag module, but with your level of Entity API compliance - which i mean string ids. To do that, we'll probably have to address Flag storage side of the medal where we can somehow intervene into table logic / queries.

I've also wrote a custom function on PGsql to fix the problem on a day 1... when problem arose. ... that was funny.

Peace.

I completely agree with Flag module issue comment 2929733-5, that this should be addressed in views and not in a contributed module. I have submitted a patch to core to address this.

Re-roll patch from #16 for 8.x-4.0-beta1

> In Drupal 8, entity IDs are strings. Period. This is true of core, and is true of the the Flag module. Changing them and redefining them to be integers is just wrong.

No, that's not how it is at all. Drupal supports both integer and string entity ID's for the most part. And most content entity ids are (auto-increment/serial) integer, not strings.

The problem is that they can also be strings, so hardcoding it either way is going to fail on some entities.

This is kinda a duplicate of #2678756: Allow config entities to be flagged, which afaik makes it dynamic, so that both integer and string can be joined, but it's challenging and a non-trivial schema change.

Applying the patch #19 to drupal core (I'm using drupal 8.9.7) fix both the problems for the view and for the add new content

This seems to be a duplicate of #2929733: SQL query error on PostgreSQL 9.5 when try to list Bookmarks.

I had this problem and was able to solve it with in the patch in #3079534-27: Views JOIN condition fails in PostgreSQL when comparing entity identifiers of different datatypes.

The patch in #27 of the core issue fixed one problem but lead to this message:

Exception in Bookmarks[bookmarks]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: "bookmark"

I fixed this by casting to text instead of bigint. It was trying to cast a text ID to int and that is an "invalid input syntax for type bigint". The updated patch is in #29 of the core issue.

Needs reroll to MR

There is a potential fix in #2929733: SQL query error on PostgreSQL 9.5 when try to list Bookmarks.