Regarding: Replication of TRUNCATE commands is not working

[Arvind Raghuwanshi]

Hi,
I have tried to run the TRUNCATE command  and found out that it's not
getting replicated using logical replication for pgsql.
I have also checked the schema change using pg_dump command but the schema
change also not getting detected for TRUNCATE command.

However on pgsql logical replication doc page[1] , it's mentioned
that Replication of TRUNCATE commands is supported.

[1] PostgreSQL: Documentation: 16: 31.6. Restrictions


Any idea how we can solve this?

Thanks
Arvind

Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

[Rajan Pandey]

Hi,
I was reading
https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
and
found that it mentions that Immutable Functions and Operators can be pushed
down using `extensions` option for foreign server.

But it does not mention about TYPE. In the shippable.c
/lookup_shippable()
function, I found that type is also pushed down.

[image: image.png]

Does this require updating the docs? Can I raise a PR to do so? Thank you!
:)

-- 
Regards
Rajan Pandey

Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

[Laurenz Albe]

On Tue, 2024-04-09 at 15:49 +0530, Rajan Pandey wrote:
> I was reading 
> https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
> and found that it mentions that Immutable Functions and Operators can
> be pushed down using `extensions` option for foreign server.
> 
> But it does not mention about TYPE. In the shippable.c/lookup_shippable()
> function, I found that type is also pushed down.

The comment only says that data types may be shippable, but not that
they are actually shipped.  Can you think of a case where a data type
would be shipped to a foreign server?  I wrote a foreign data wrapper,
and I cannot think of such a case.

Perhaps the function comment should be adjusted by removing the parenthesis
or changing it to "(operator/function/...)".

> Does this require updating the docs? Can I raise a PR to do so? Thank you! :) 

You would send a patch against the "master" branch to the pgsql-docs list for 
that.

Yours,
Laurenz Albe

Re: Regarding: Replication of TRUNCATE commands is not working

[Laurenz Albe]

On Tue, 2024-04-09 at 14:50 +0530, Arvind Raghuwanshi wrote:
> I have tried to run the TRUNCATE command  and found out that it's not getting 
> replicated using logical replication for pgsql.
> I have also checked the schema change using pg_dump command but the schema 
> change also not getting detected for TRUNCATE command.
> 
> However on pgsql logical replication doc page[1] , it's mentioned that 
> Replication of TRUNCATE commands is supported.
> 
> [1] PostgreSQL: Documentation: 16: 31.6. Restrictions
> 
> Any idea how we can solve this?

What PostgreSQL version are you using?  The feature was introduced in v11.
How exactly is the publication defined?  Perhaps TRUNCATE is excluded.

Yours,
Laurenz Albe

Re: Regarding: Replication of TRUNCATE commands is not working

[Arvind Raghuwanshi]

Hi Laurenz,
Thanks for the response
Question: What PostgreSQL version are you using?  The feature was
introduced in v11.
Answer: I am using the 16.0 Postgresql version.
db1=> SELECT version();
   version
-
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

Question: How exactly is the publication defined?  Perhaps TRUNCATE is
excluded.
I am not using the subscribe/publication model . but i have created a
replication slot on the source database and then i am calling below
procedure to get the details from replication slot:
select data from pg_logical_slot_peek_changes('db1_slot', NULL, NULL,
'pretty-print', '1');

In the case of TRUNCATE , the above procedure does not show up any records.
however this procedure shows up insert, update and delete events.

As you mentioned TRUNCATE is excluded, is there any way to exclude
TRUNCATE ?

Thanks
Arvind

On Tue, Apr 9, 2024 at 4:08 PM Laurenz Albe 
wrote:

> On Tue, 2024-04-09 at 14:50 +0530, Arvind Raghuwanshi wrote:
> > I have tried to run the TRUNCATE command  and found out that it's not
> getting replicated using logical replication for pgsql.
> > I have also checked the schema change using pg_dump command but the
> schema change also not getting detected for TRUNCATE command.
> >
> > However on pgsql logical replication doc page[1] , it's mentioned
> that Replication of TRUNCATE commands is supported.
> >
> > [1] PostgreSQL: Documentation: 16: 31.6. Restrictions
> >
> > Any idea how we can solve this?
>
> What PostgreSQL version are you using?  The feature was introduced in v11.
> How exactly is the publication defined?  Perhaps TRUNCATE is excluded.
>
> Yours,
> Laurenz Albe
>

Tracing libpq client: Only with PQtrace()?

[Sebastien Flaesch]

Hello

Sorry if I missed that in the doc:

Is the PQtrace() API the only way to enable libpq client tracing?

I thought about some environment variable of client configuration setting...

Seb

Re: Tracing libpq client: Only with PQtrace()?

[Alvaro Herrera]

Hello,

On 2024-Apr-09, Sebastien Flaesch wrote:

> Is the PQtrace() API the only way to enable libpq client tracing?

Yes.

Regards

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

Re: Regarding: Replication of TRUNCATE commands is not working

[Adrian Klaver]

On 4/9/24 05:07, Arvind Raghuwanshi wrote:

Hi Laurenz,
Thanks for the response
Question: What PostgreSQL version are you using?  The feature was 
introduced in v11.

Answer: I am using the 16.0 Postgresql version.
db1=> SELECT version();
                                                version
-
  PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

(1 row)

Question: How exactly is the publication defined?  Perhaps TRUNCATE is 
excluded.
I am not using the subscribe/publication model . but i have created a 


To be clear you have not done CREATE PUBLICATION on the source machine, 
correct?


What is the rest of your configuration per:

https://www.postgresql.org/docs/current/logical-replication-config.html

Also what exactly is being TRUNCTEd?


replication slot on the source database and then i am calling below 
procedure to get the details from replication slot:
select data from pg_logical_slot_peek_changes('db1_slot', NULL, NULL, 
'pretty-print', '1');


In the case of TRUNCATE , the above procedure does not show up any 
records. however this procedure shows up insert, update and delete events.


As you mentioned TRUNCATE is excluded, is there any way to exclude 
TRUNCATE ?


Thanks
Arvind

On Tue, Apr 9, 2024 at 4:08 PM Laurenz Albe > wrote:


On Tue, 2024-04-09 at 14:50 +0530, Arvind Raghuwanshi wrote:
 > I have tried to run the TRUNCATE command  and found out that it's
not getting replicated using logical replication for pgsql.
 > I have also checked the schema change using pg_dump command but
the schema change also not getting detected for TRUNCATE command.
 >
 > However on pgsql logical replication doc page[1] , it's mentioned
that Replication of TRUNCATE commands is supported.
 >
 > [1] PostgreSQL: Documentation: 16: 31.6. Restrictions
 >
 > Any idea how we can solve this?

What PostgreSQL version are you using?  The feature was introduced
in v11.
How exactly is the publication defined?  Perhaps TRUNCATE is excluded.

Yours,
Laurenz Albe



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: prepared statement "cu1" already exists (but it does not)

[Sebastien Flaesch]

Tom,

> > Is it an issue if I use the same name for a prepared statement and the 
> > server cursor? I mean:
> From memory, I think those share the same "portal" namespace.

Can you please elaborate?

Is it supported to do:

   PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
   PQexecPrepared(pgConn, "cu1", ... )

?

So far this has always worked.

Seb



From: Tom Lane 
Sent: Monday, April 8, 2024 7:36 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: prepared statement "cu1" already exists (but it does not)

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch  writes:
> I understand that the whole TX is aborted with PostgreSQL, and probably the 
> deallocate is useless since stmt was prepared inside the TX?

As you can quickly discover with some manual experimentation,
both PREPARE and DEALLOCATE are nontransactional, in the sense
that if they succeed then the prepared statement will exist
(or not) even if the surrounding transaction block is later
rolled back.  This is pretty weird, and undocumented I think,
in terms of their role as SQL statements.
It makes a little more sense if you think about the equivalent
wire-protocol-level operations, which are meant to be used
by low-level client code that may not be aware of whether
there is a transaction block in progress.

> Is it an issue if I use the same name for a prepared statement and the server 
> cursor? I mean:

>From memory, I think those share the same "portal" namespace.

regards, tom lane

Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

[Rajan Pandey]

Hi team 😃.

In the
https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
doc, it
mentions that Immutable Functions and Hi can be pushed down using
`extensions` option for foreign server.

But it does not mention TYPE. In the shippable.c
/lookup_shippable()
function, I found a comment that indicates that type is also pushed down.
Hece, I have removed TYPE from the comments, assuming that it does not make
sense to push down a data type, and only functions and operations can be
pushed down. Please let me know if my assumption is incorrect. 🙂

I have added my patch file with the mail. Thanks!

On Tue, Apr 9, 2024 at 4:06 PM Laurenz Albe 
wrote:

> On Tue, 2024-04-09 at 15:49 +0530, Rajan Pandey wrote:
> > I was reading
> https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
> > and found that it mentions that Immutable Functions and Operators can
> > be pushed down using `extensions` option for foreign server.
> >
> > But it does not mention about TYPE. In the shippable.c/lookup_shippable()
> > function, I found that type is also pushed down.
>
> The comment only says that data types may be shippable, but not that
> they are actually shipped.  Can you think of a case where a data type
> would be shipped to a foreign server?  I wrote a foreign data wrapper,
> and I cannot think of such a case.
>
> Perhaps the function comment should be adjusted by removing the parenthesis
> or changing it to "(operator/function/...)".
>
> > Does this require updating the docs? Can I raise a PR to do so? Thank
> you! :)
>
> You would send a patch against the "master" branch to the pgsql-docs list
> for that.
>
> Yours,
> Laurenz Albe
>


-- 
Regards
Rajan Pandey


update_lookup_shippable_comments.patch
Description: Binary data

Re: Tracing libpq client: Only with PQtrace()?

[Greg Sabino Mullane]

On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch 
wrote:

> Is the PQtrace() API the only way to enable libpq client tracing?
>
> I thought about some environment variable of client configuration
> setting...
>

That's generally the job of the client, or more specifically, the driver
providing the interface between your program and the libpq API. Unless you
are writing at the low level yourself, in which case, you would need to
have your program detect an env and call PQtrace() as needed.

See for example
https://metacpan.org/pod/DBD::Pg#parse_trace_flag-and-parse_trace_flags

Cheers,
Greg

[Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Thiemo Kellner]

Hi

I have the following function code. When trying to install, it gives me

[Code: 0, SQL State: 0A000]  FEHLER: Verweise auf andere Datenbanken 
sind nicht implementiert: pg_catalog.pg_roles.rolname

  Position: 298  [Script position: 334 - 361]

To the best of my knowledge, pg_catalog is a schema not a database, 
like information_schema. Am I missing something? And why is it not 
allowed to type from the catalogue?


I presume, this example is rather academic due to the name type.

Kind regards

Thiemo


create or replace function GRANT_SELECTS()
returns void
language plpgsql
as
$body$
declare
C_SCHEMA_NAME   constant 
INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME%type :=

  'snowrunner';
--C_ROLE_NAME constantname :=
C_ROLE_NAME constantPG_CATALOG.PG_ROLES.ROLNAME :=
  'snowrunner_reader';
V_SQL_STATEMENT text;
begin
-- Check the existance of the schema
perform 1
from INFORMATION_SCHEMA.SCHEMATA
where SCHEMA_NAME = C_SCHEMA_NAME;
if not found then
raise exception 'Schema "%s" does not exist!', C_SCHEMA_NAME;
end if;

-- Check the existance of the role
perform 1
from PG_CATALOG.PG_ROLES
where ROLNAME = C_ROLE_NAME;
if not found then
raise exception 'Role "%s" does not exist!', C_ROLE_NAME;
end if;

-- Issue grants
V_SQL_STATEMENT := format('grant select on all tables in schema 
%i to %i', C_SCHEMA_NAME, C_ROLE_NAME);

raise info '%', V_SQL_STATEMENT;
execute V_SQL_STATEMENT;
V_SQL_STATEMENT := format('grant select on all views in schema 
%i to %i', C_SCHEMA_NAME, C_ROLE_NAME);

raise info '%', V_SQL_STATEMENT;
execute V_SQL_STATEMENT;
V_SQL_STATEMENT := format('grant select on all materialized 
views in schema %i to %i', C_SCHEMA_NAME, C_ROLE_NAME);

raise info '%', V_SQL_STATEMENT;
execute V_SQL_STATEMENT;
commit;

return;
end;
$body$;

Re: Tracing libpq client: Only with PQtrace()?

[Alvaro Herrera]

On 2024-Apr-09, Greg Sabino Mullane wrote:

> On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch 
> wrote:
> 
> > Is the PQtrace() API the only way to enable libpq client tracing?
> >
> > I thought about some environment variable of client configuration
> > setting...
> 
> That's generally the job of the client, or more specifically, the driver
> providing the interface between your program and the libpq API. Unless you
> are writing at the low level yourself, in which case, you would need to
> have your program detect an env and call PQtrace() as needed.
> 
> See for example
> https://metacpan.org/pod/DBD::Pg#parse_trace_flag-and-parse_trace_flags

Another approach might be to use Wireshark, which has a decoder for the
Postgres FE/BE protocol.

-- 
Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Adrian Klaver]

On 4/9/24 07:59, Thiemo Kellner wrote:

Hi

I have the following function code. When trying to install, it gives me

[Code: 0, SQL State: 0A000]  FEHLER: Verweise auf andere Datenbanken 
sind nicht implementiert: pg_catalog.pg_roles.rolname

   Position: 298  [Script position: 334 - 361]


[Code: 0, SQL State: 0A000] ERROR: References to other databases are not 
implemented: pg_catalog.pg_roles.rolname

   Position: 298 [Script position: 334 - 361]


To the best of my knowledge, pg_catalog is a schema not a database, like 
information_schema. Am I missing something? And why is it not allowed to 
type from the catalogue?


I presume, this example is rather academic due to the name type.


PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader';

Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME.





Kind regards

Thiemo


create or replace function GRANT_SELECTS()
returns void
language plpgsql
as
$body$
     declare
     C_SCHEMA_NAME   constant 
INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME%type :=

   'snowrunner';
--    C_ROLE_NAME constant    name :=
     C_ROLE_NAME constant    PG_CATALOG.PG_ROLES.ROLNAME :=
   'snowrunner_reader';
     V_SQL_STATEMENT text;
     begin
     -- Check the existance of the schema
     perform 1
     from INFORMATION_SCHEMA.SCHEMATA
     where SCHEMA_NAME = C_SCHEMA_NAME;
     if not found then
     raise exception 'Schema "%s" does not exist!', C_SCHEMA_NAME;
     end if;

     -- Check the existance of the role
     perform 1
     from PG_CATALOG.PG_ROLES
     where ROLNAME = C_ROLE_NAME;
     if not found then
     raise exception 'Role "%s" does not exist!', C_ROLE_NAME;
     end if;

     -- Issue grants
     V_SQL_STATEMENT := format('grant select on all tables in schema 
%i to %i', C_SCHEMA_NAME, C_ROLE_NAME);

     raise info '%', V_SQL_STATEMENT;
     execute V_SQL_STATEMENT;
     V_SQL_STATEMENT := format('grant select on all views in schema 
%i to %i', C_SCHEMA_NAME, C_ROLE_NAME);

     raise info '%', V_SQL_STATEMENT;
     execute V_SQL_STATEMENT;
     V_SQL_STATEMENT := format('grant select on all materialized 
views in schema %i to %i', C_SCHEMA_NAME, C_ROLE_NAME);

     raise info '%', V_SQL_STATEMENT;
     execute V_SQL_STATEMENT;
     commit;

     return;
     end;
$body$;




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Thiemo Kellner]

Thanks for taking this up.

Am 09.04.2024 um 17:09 schrieb Adrian Klaver:

On 4/9/24 07:59, Thiemo Kellner wrote:
[Code: 0, SQL State: 0A000] ERROR: References to other databases are not 
implemented: pg_catalog.pg_roles.rolname

    Position: 298 [Script position: 334 - 361]

PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader';

Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME.


Yes, obviously, but why? With the information_schema view all is fine. 
And, I suppose, with all other objects in other schemas of the same 
database too.

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Adrian Klaver]

On 4/9/24 08:12, Thiemo Kellner wrote:

Thanks for taking this up.

Am 09.04.2024 um 17:09 schrieb Adrian Klaver:

On 4/9/24 07:59, Thiemo Kellner wrote:
[Code: 0, SQL State: 0A000] ERROR: References to other databases are 
not implemented: pg_catalog.pg_roles.rolname

    Position: 298 [Script position: 334 - 361]

PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader';

Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME.


Yes, obviously, but why? With the information_schema view all is fine. 
And, I suppose, with all other objects in other schemas of the same 
database too.


Because you did not do?:

PG_CATALOG.PG_ROLES.ROLNAME%type
--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Tracing libpq client: Only with PQtrace()?

[Sebastien Flaesch]

Yep I got it, thanks for the suggestion!
Seb

From: Greg Sabino Mullane 
Sent: Tuesday, April 9, 2024 4:50 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: Tracing libpq client: Only with PQtrace()?


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Tue, Apr 9, 2024 at 8:51 AM Sebastien Flaesch 
mailto:sebastien.flae...@4js.com>> wrote:
Is the PQtrace() API the only way to enable libpq client tracing?

I thought about some environment variable of client configuration setting...

That's generally the job of the client, or more specifically, the driver 
providing the interface between your program and the libpq API. Unless you are 
writing at the low level yourself, in which case, you would need to have your 
program detect an env and call PQtrace() as needed.

See for example 
https://metacpan.org/pod/DBD::Pg#parse_trace_flag-and-parse_trace_flags

Cheers,
Greg

Issue with date/timezone conversion function

[Lok P]

Hi All,
It's version 15.4 of postgresql database. Every "date/time" data type
attribute gets stored in the database in UTC timezone only. One of the
support persons local timezone is "asia/kolkata" and  that support person
needs to fetch the count of transactions from a table- transaction_tab and
share it with another person/customer who is in the EST timezone, so
basically the transaction has to be shown or displayed the EST timezone.

We are using below three queries for displaying each 15 minutes , hourly
and daily interval transaction counts from the table based on the
create_timestamp column (in which data is stored in UTC timezone in the
database but it has to be displayed to the user in EST timezone).

These tables are INSERT only tables and the data in the create_timestamp
column is populated using the now() function from the application, which
means it will always be incremental, and the historical day transaction
count is going to be the same. However surprisingly the counts are changing
each day when the user fetches the result using the below query. So my
question was , if there is any issue with the way we are fetching the data
and it's making some date/time shift which is why the transaction count
looks to be changing even for the past days data? And also somehow this
conversion function "DATE_TRUNC('hour', create_timestamp AT TIME ZONE
'EST')" is showing time in CST but not EST, why so?

**For fetching 15 minutes interval transaction count

SELECT
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST') +
(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE 'EST') / 15 * 15) *
INTERVAL '15 minute' AS sample_time_interval,
COUNT(*) AS count_1
from transaction_tab
WHERE create_timestamp > CURRENT_TIMESTAMP - INTERVAL '10 day'
GROUP BY
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST') +
(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE 'EST') / 15 * 15) *
INTERVAL '15 minute'
ORDER BY sample_time_interval;

**For fetching hourly interval transaction count

SELECT
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST') AS
sample_time_interval,
COUNT(*) AS count_1
from  transaction_tab
WHERE create_timestamp > CURRENT_TIMESTAMP - INTERVAL '10 day'
GROUP BY
DATE_TRUNC('hour', create_timestamp AT TIME ZONE 'EST')
ORDER BY sample_time_interval;

**For fetching daily interval transaction count

SELECT
DATE_TRUNC('day', create_timestamp AT TIME ZONE 'EST') AS
sample_time_interval,
COUNT(*) AS count_1
from  transaction_tab
WHERE create_timestamp > CURRENT_TIMESTAMP - INTERVAL '10 day'
GROUP BY
DATE_TRUNC('day', create_timestamp AT TIME ZONE 'EST')
ORDER BY sample_time_interval;

Re: Issue with date/timezone conversion function

[Adrian Klaver]

On 4/9/24 08:43, Lok P wrote:

Hi All,
It's version 15.4 of postgresql database. Every "date/time" data type 
attribute gets stored in the database in UTC timezone only. One of the 
support persons local timezone is "asia/kolkata" and  that support 
person needs to fetch the count of transactions from a table- 
transaction_tab and share it with another person/customer who is in the 
EST timezone, so basically the transaction has to be shown or displayed 
the EST timezone.


What is the datatype for the create_timestamp?

What does SHOW timezone; return on the server?



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Issue with date/timezone conversion function

[Lok P]

On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver 
wrote:

> On 4/9/24 08:43, Lok P wrote:
> > Hi All,
> > It's version 15.4 of postgresql database. Every "date/time" data type
> > attribute gets stored in the database in UTC timezone only. One of the
> > support persons local timezone is "asia/kolkata" and  that support
> > person needs to fetch the count of transactions from a table-
> > transaction_tab and share it with another person/customer who is in the
> > EST timezone, so basically the transaction has to be shown or displayed
> > the EST timezone.
>
> What is the datatype for the create_timestamp?
>
> What does SHOW timezone; return on the server?
>
>
>
Thank you for the quick response.

The column data type for "create_timestamp" is "timestamptz'.
Show timezone from the support users client machine UI showing
"Asia/Calcutta".
Not having access to run "Show timezone" on the server currently, I will
try to get it.
output from pg_setting showing setting as "Asia/Calcutta', reset_val as
"Asia/Calcutta", boot_val as "GMT"

PL/pgSQL techniques better than bash for dynamic DO?

[Ron Johnson]

PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now.

I must purge the oldest X period of records from 70 tables, every Sunday.
The field name, interval (X days or months) and date (CURRENT_DATE or
CURRENT_TIMESTAMP) varies for each table.
Thus, I put all the relevant data in a tab-separated value file, and use
bash to read through it, purging one table at a time.  This works well,
except for Foreign Key constraints; carefully ordering the file to purge
records in the correct order eliminates most FK errors, but not all.

Therefore, I created an anonymous DO statement to delete the "deletable"
old records, while skipping the ones that would fail from a FK constraint.
(Eventually, the records in the FK table will get deleted, so eventually
the records who's DELETE failed will succeed in getting deleted.)

(NOTE: I cannot change the FK constraints to ON DELETE CASCADE, and nor do
I want to fight with the 3rd party app vendor, since it defeats the purpose
of FK constraints.)

Here's the snippet of bash code:
local Schema=$1
local Table=$2
local Field=$3
local DtCol=$4  # CURRENT_TIMESTAMP or CURRENT_DATE
local Thresh=$5 # example: '90 day'
local FQTable=${Schema}.${Table}
DeS="DO \$\$
DECLARE
delsum INTEGER = 0;
delcnt INTEGER;
skipsum integer = 0;
cur_row CURSOR FOR
SELECT $Field, ${Table}_id
from ${FQTable}
where $Field < (${DtCol} - interval ${Thresh});
BEGIN
FOR arow IN cur_row
LOOP
BEGIN
DELETE FROM ${FQTable} WHERE CURRENT OF cur_row;
GET DIAGNOSTICS delcnt = ROW_COUNT;
delsum = delsum + delcnt;EXCEPTION
WHEN others THEN
skipsum = skipsum + 1;
RAISE NOTICE 'Skipped ${FQTable} WHERE ${Table}_id = %;
${Field} = %',
arow.${Table}_id, arow.${Field};
END;
END LOOP;
RAISE NOTICE 'Sum of deleted rows: %', delsum;
RAISE NOTICE 'Sum of skipped rows: %', skipsum;
END \$\$;
"

It generates the perfectly functional SQL:
DO $$
DECLARE
delsum INTEGER = 0;
delcnt INTEGER;
skipsum integer = 0;
cur_row CURSOR FOR
SELECT modified_on, check_id
from tms.check
where modified_on < (CURRENT_TIMESTAMP - interval '90 day');
BEGIN
FOR arow IN cur_row
LOOP
BEGIN
DELETE FROM tms.check WHERE CURRENT OF cur_row;
GET DIAGNOSTICS delcnt = ROW_COUNT;
delsum = delsum + delcnt;
EXCEPTION
WHEN others THEN
skipsum = skipsum + 1;
RAISE NOTICE 'Skipped tms.check WHERE check_id = %;
modified_on = %',
arow.check_id, arow.modified_on;
END;
END LOOP;
RAISE NOTICE 'Sum of deleted rows: %', delsum;
RAISE NOTICE 'Sum of skipped rows: %', skipsum;
END $$;

Can I do this better in PL/pgSQL with dynamic SQL (that doesn't get hairy
with nested quotes, etc)?

Re: Moving delta data faster

[yudhi s]

On Sun, Apr 7, 2024 at 2:25 AM Adrian Klaver 
wrote:

>
> I have no idea how this works in the code, but my suspicion is it is due
> to the following:
>
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
>
> "The optional ON CONFLICT clause specifies an alternative action to
> raising a unique violation or exclusion constraint violation error. For
> each individual row proposed for insertion, either the insertion
> proceeds, or, if an arbiter constraint or index specified by
> conflict_target is violated, the alternative conflict_action is taken.
> ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative
> action. ON CONFLICT DO UPDATE updates the existing row that conflicts
> with the row proposed for insertion as its alternative action."
>
> vs this:
>
> "First, the MERGE command performs a join from data_source to
> target_table_name producing zero or more candidate change rows. For each
> candidate change row, the status of MATCHED or NOT MATCHED is set just
> once, after which WHEN clauses are evaluated in the order specified. For
> each candidate change row, the first clause to evaluate as true is
> executed. No more than one WHEN clause is executed for any candidate
> change row."
>
> Where ON CONFLICT attempts the INSERT then on failure does the UPDATE
> for the ON CONFLICT DO UPDATE case. MERGE on the hand evaluates based on
> the join condition(ON tbl1.fld =tbl2.fld) and then based on MATCH/NOT
> MATCHED takes the appropriate action for the first WHEN match. In other
> words it goes directly to the appropriate action.
>
>
Thank you Adrian. I think you are spoton on the cause of upsert becoming
slower than Merge. Below is the explain plan I captured for both the
operations and it looks like even the planning time is small for the
UPSERT, as because it operates on the constraint i.e the Update will wait
for all the failure records from the INSERT and thus it takes longer. The
Merge seems to be evaluated on the Joins i.e it is directly able to get the
set of rows which has to be Updated rather waiting for the INSERT to make
it fail based on the PK constraint.

** Explain plan for UPSERT *

Insert on public.target_tab (cost=0.00..17353.00 rows=0 width=0) (actual
time=19957.569..19957.570 rows=0 loops=1)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: target_tab_pkey
Tuples Inserted: 50
Conflicting Tuples: 50
Buffers: shared hit=8545735 written=10094
-> Seq Scan on public.source_tab (cost=0.00..17353.00 rows=100
width=29) (actual time=0.006..208.306 rows=100 loops=1)
Output: source_tab.id, source_tab.column1, source_tab.column2
Buffers: shared hit=7353
Settings: effective_cache_size = '10475192kB', maintenance_io_concurrency =
'1', max_parallel_workers = '32', max_parallel_workers_per_gather = '4',
search_path = 'public, public, "$user"', temp_buffers = '16MB', work_mem =
'8MB'
Query Identifier: -1356019529835809419
Planning:
Buffers: shared hit=41
Planning Time: 0.199 ms
Execution Time: 19959.261 ms

** Explain plan for Merge *

Merge on public.target_tab t (cost=17368.00..53460.01 rows=0 width=0)
(actual time=14209.966..14209.968 rows=0 loops=1)
Tuples: inserted=50 updated=50
Buffers: shared hit=5040097 written=10460, temp read=4143 written=4143
I/O Timings: temp read=26.746 write=68.596
-> Hash Left Join (cost=17368.00..53460.01 rows=100 width=35) (actual
time=179.233..1332.264 rows=100 loops=1)
Output: t.ctid, s.column1, s.column2, s.id
Inner Unique: true
Hash Cond: (s.id = t.id)
Buffers: shared hit=11029, temp read=4143 written=4143
I/O Timings: temp read=26.746 write=68.596
-> Seq Scan on public.source_tab s (cost=0.00..17353.00 rows=100
width=29) (actual time=0.008..268.506 rows=100 loops=1)
Output: s.column1, s.column2, s.id
Buffers: shared hit=7353
-> Hash (cost=8676.00..8676.00 rows=50 width=10) (actual
time=178.101..178.102 rows=50 loops=1)
Output: t.ctid, t.id
Buckets: 524288 Batches: 2 Memory Usage: 14824kB
Buffers: shared hit=3676, temp written=977
I/O Timings: temp write=5.904
-> Seq Scan on public.target_tab t (cost=0.00..8676.00 rows=50
width=10) (actual time=0.007..66.441 rows=50 loops=1)
Output: t.ctid, t.id
Buffers: shared hit=3676
Settings: effective_cache_size = '10475192kB', maintenance_io_concurrency =
'1', max_parallel_workers = '32', max_parallel_workers_per_gather = '4',
search_path = 'public, public, "$user"', temp_buffers = '16MB', work_mem =
'8MB'
Query Identifier: -2297080081674771467
Planning:
Buffers: shared hit=85
Planning Time: 0.466 ms
Execution Time: 14212.061 ms

Re: prepared statement "cu1" already exists (but it does not)

[Tom Lane]

Sebastien Flaesch  writes:
>>> Is it an issue if I use the same name for a prepared statement and the 
>>> server cursor? I mean:

>> From memory, I think those share the same "portal" namespace.

> Can you please elaborate?

> Is it supported to do:
>PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
>PQexecPrepared(pgConn, "cu1", ... )
> ?

> So far this has always worked.

Ah, sorry, what I should have said is that the SQL PREPARE command
works with the same namespace as PQprepare (which is a thin wrapper
over the wire-protocol Parse command).  There is a different namespace
for cursors.  Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.

regards, tom lane

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Thiemo Kellner]

Am 09.04.2024 um 17:18 schrieb Adrian Klaver:

Because you did not do?:

PG_CATALOG.PG_ROLES.ROLNAME%type


Oh, right. Sorry. What an oversight.

Re: prepared statement "cu1" already exists (but it does not)

[Sebastien Flaesch]

Ok thanks Tom,

My understanding from your last comment:

Since prepared statements and server cursors use different name spaces, it's 
save to use the same name as in

PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )

Seb

From: Tom Lane 
Sent: Tuesday, April 9, 2024 6:36 PM
To: Sebastien Flaesch 
Cc: pgsql-general 
Subject: Re: prepared statement "cu1" already exists (but it does not)

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch  writes:
>>> Is it an issue if I use the same name for a prepared statement and the 
>>> server cursor? I mean:

>> From memory, I think those share the same "portal" namespace.

> Can you please elaborate?

> Is it supported to do:
>PQprepare(pgConn, "cu1", "declare cu1 cursor for ... ", ... )
>PQexecPrepared(pgConn, "cu1", ... )
> ?

> So far this has always worked.

Ah, sorry, what I should have said is that the SQL PREPARE command
works with the same namespace as PQprepare (which is a thin wrapper
over the wire-protocol Parse command).  There is a different namespace
for cursors.  Cursors do share that namespace with the wire-protocol
"portal" concept, but libpq doesn't directly expose portals.

regards, tom lane

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Thiemo Kellner]

Am 09.04.2024 um 17:18 schrieb Adrian Klaver:


Because you did not do?:

PG_CATALOG.PG_ROLES.ROLNAME%type


Thanks

Re: Issue with date/timezone conversion function

[Adrian Klaver]

On 4/9/24 9:16 AM, Lok P wrote:


On Tue, Apr 9, 2024 at 9:26 PM Adrian Klaver > wrote:


On 4/9/24 08:43, Lok P wrote:
 > Hi All,
 > It's version 15.4 of postgresql database. Every "date/time" data
type
 > attribute gets stored in the database in UTC timezone only. One
of the
 > support persons local timezone is "asia/kolkata" and  that support
 > person needs to fetch the count of transactions from a table-
 > transaction_tab and share it with another person/customer who is
in the
 > EST timezone, so basically the transaction has to be shown or
displayed
 > the EST timezone.

What is the datatype for the create_timestamp?

What does SHOW timezone; return on the server?



Thank you for the quick response.

The column data type for "create_timestamp" is "timestamptz'.
Show timezone from the support users client machine UI showing  
"Asia/Calcutta".
Not having access to run "Show timezone" on the server currently, I will 
try to get it.
output from pg_setting showing setting as "Asia/Calcutta', reset_val as 
"Asia/Calcutta", boot_val as "GMT"


In the pg_settings query what are the source, sourcefile, sourceline 
fields set to?




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Issue with date/timezone conversion function

[Tom Lane]

Lok P  writes:
> These tables are INSERT only tables and the data in the create_timestamp
> column is populated using the now() function from the application, which
> means it will always be incremental, and the historical day transaction
> count is going to be the same. However surprisingly the counts are changing
> each day when the user fetches the result using the below query. So my
> question was , if there is any issue with the way we are fetching the data
> and it's making some date/time shift which is why the transaction count
> looks to be changing even for the past days data?

Well, your cutoff time "CURRENT_TIMESTAMP - INTERVAL '10 day'" is
constantly moving, so that'd account for shifts in what's perceived
to belong to the oldest day.  Maybe you want "CURRENT_DATE - 10"
instead?

> And also somehow this
> conversion function "DATE_TRUNC('hour', create_timestamp AT TIME ZONE
> 'EST')" is showing time in CST but not EST, why so?

'EST' is going to rotate to UTC-5, but that's probably not what
you want in the summer.  I'd suggest AT TIME ZONE 'America/New_York'
or the like.  See

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

regards, tom lane

Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog

[Tom Lane]

Adrian Klaver  writes:
> On 4/9/24 08:12, Thiemo Kellner wrote:
>>> PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader';
>>> 
>>> Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME.

No, it's being seen as DB_NAME.SCHEMA_NAME.TYPE_NAME.

> Because you did not do?:
> PG_CATALOG.PG_ROLES.ROLNAME%type

Exactly.  The %type bit is important.

regards, tom lane

Re: PL/pgSQL techniques better than bash for dynamic DO?

[Pavel Stehule]

út 9. 4. 2024 v 18:33 odesílatel Ron Johnson 
napsal:

> PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now.
>
> I must purge the oldest X period of records from 70 tables, every Sunday.
> The field name, interval (X days or months) and date (CURRENT_DATE or
> CURRENT_TIMESTAMP) varies for each table.
> Thus, I put all the relevant data in a tab-separated value file, and use
> bash to read through it, purging one table at a time.  This works well,
> except for Foreign Key constraints; carefully ordering the file to purge
> records in the correct order eliminates most FK errors, but not all.
>
> Therefore, I created an anonymous DO statement to delete the "deletable"
> old records, while skipping the ones that would fail from a FK constraint.
> (Eventually, the records in the FK table will get deleted, so eventually
> the records who's DELETE failed will succeed in getting deleted.)
>
> (NOTE: I cannot change the FK constraints to ON DELETE CASCADE, and nor do
> I want to fight with the 3rd party app vendor, since it defeats the purpose
> of FK constraints.)
>
> Here's the snippet of bash code:
> local Schema=$1
> local Table=$2
> local Field=$3
> local DtCol=$4  # CURRENT_TIMESTAMP or CURRENT_DATE
> local Thresh=$5 # example: '90 day'
> local FQTable=${Schema}.${Table}
> DeS="DO \$\$
> DECLARE
> delsum INTEGER = 0;
> delcnt INTEGER;
> skipsum integer = 0;
> cur_row CURSOR FOR
> SELECT $Field, ${Table}_id
> from ${FQTable}
> where $Field < (${DtCol} - interval ${Thresh});
> BEGIN
> FOR arow IN cur_row
> LOOP
> BEGIN
> DELETE FROM ${FQTable} WHERE CURRENT OF cur_row;
> GET DIAGNOSTICS delcnt = ROW_COUNT;
> delsum = delsum + delcnt;EXCEPTION
> WHEN others THEN
> skipsum = skipsum + 1;
> RAISE NOTICE 'Skipped ${FQTable} WHERE ${Table}_id =
> %; ${Field} = %',
> arow.${Table}_id, arow.${Field};
> END;
> END LOOP;
> RAISE NOTICE 'Sum of deleted rows: %', delsum;
> RAISE NOTICE 'Sum of skipped rows: %', skipsum;
> END \$\$;
> "
>
> It generates the perfectly functional SQL:
> DO $$
> DECLARE
> delsum INTEGER = 0;
> delcnt INTEGER;
> skipsum integer = 0;
> cur_row CURSOR FOR
> SELECT modified_on, check_id
> from tms.check
> where modified_on < (CURRENT_TIMESTAMP - interval '90 day');
> BEGIN
> FOR arow IN cur_row
> LOOP
> BEGIN
> DELETE FROM tms.check WHERE CURRENT OF cur_row;
> GET DIAGNOSTICS delcnt = ROW_COUNT;
> delsum = delsum + delcnt;
> EXCEPTION
> WHEN others THEN
> skipsum = skipsum + 1;
> RAISE NOTICE 'Skipped tms.check WHERE check_id = %;
> modified_on = %',
> arow.check_id, arow.modified_on;
> END;
> END LOOP;
> RAISE NOTICE 'Sum of deleted rows: %', delsum;
> RAISE NOTICE 'Sum of skipped rows: %', skipsum;
> END $$;
>
> Can I do this better in PL/pgSQL with dynamic SQL (that doesn't get hairy
> with nested quotes, etc)?
>

you can pass values by GUC instead

pavel@nemesis:~$ psql -v var="AHOJ"
Assertions: on
psql (17devel)
Type "help" for help.

(2024-04-09 19:07:55) postgres=# select set_config('my.var', :'var', false);
┌┐
│ set_config │
╞╡
│ AHOJ   │
└┘
(1 row)

(2024-04-09 19:08:46) postgres=# do $$
postgres$# declare myvar varchar default current_setting('my.var');
postgres$# begin
postgres$#   raise notice '%', myvar;
postgres$# end;
postgres$# $$;
NOTICE:  AHOJ
DO

Regards

Pavel

Re: Issue with date/timezone conversion function

[Lok P]

On Tue, Apr 9, 2024 at 10:33 PM Tom Lane  wrote:

> Lok P  writes:
> > These tables are INSERT only tables and the data in the create_timestamp
> > column is populated using the now() function from the application, which
> > means it will always be incremental, and the historical day transaction
> > count is going to be the same. However surprisingly the counts are
> changing
> > each day when the user fetches the result using the below query. So my
> > question was , if there is any issue with the way we are fetching the
> data
> > and it's making some date/time shift which is why the transaction count
> > looks to be changing even for the past days data?
>
> Well, your cutoff time "CURRENT_TIMESTAMP - INTERVAL '10 day'" is
> constantly moving, so that'd account for shifts in what's perceived
> to belong to the oldest day.  Maybe you want "CURRENT_DATE - 10"
> instead?
>
> > And also somehow this
> > conversion function "DATE_TRUNC('hour', create_timestamp AT TIME ZONE
> > 'EST')" is showing time in CST but not EST, why so?
>
> 'EST' is going to rotate to UTC-5, but that's probably not what
> you want in the summer.  I'd suggest AT TIME ZONE 'America/New_York'
> or the like.  See
>
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
>
> regards, tom lane
>


 Thank you so much. You are correct. The AT TIME ZONE 'America/New_York' is
giving correct EST time conversion.

But I think regarding why it looks to be shifting i.e. the same time
duration appears to be holding a different count of transactions while the
base table is not getting updated/inserted/deleted for its historical
create_timestamps, I suspect the below conversion part.

The task is to count each ~15minutes duration transaction and publish in
ordered fashion i.e. something as below, but the way it's been written
seems wrong. It's an existing script. It first gets the date component with
truncated hour and then adds the time component to it to make it ~15minutes
interval. Can it be written in some simple way?

9-apr-2024 14:00 12340
9-apr-2024 14:15 12312
9-apr-2024 14:30 12323
9-apr-2024 14:45 12304

*DATE_TRUNC('hour', create_timestamp AT TIME ZONE '*America/New_York'*) +*
*(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE '*America/New_York*') /
15 * 15) * INTERVAL '15 minute'*

Re: Issue with date/timezone conversion function

[yudhi s]

Below should work...

date_trunc('hour', timestamp_column *AT TIME ZONE '*America/New_York')
+ (((date_part('minute', timestamp_column *AT TIME ZONE
'*America/New_York')::int
/ 15)::int) * interval '15 min')

On Tue, Apr 9, 2024 at 11:54 PM Lok P  wrote:

>
> On Tue, Apr 9, 2024 at 10:33 PM Tom Lane  wrote:
>
>> Lok P  writes:
>> > These tables are INSERT only tables and the data in the create_timestamp
>> > column is populated using the now() function from the application, which
>> > means it will always be incremental, and the historical day transaction
>> > count is going to be the same. However surprisingly the counts are
>> changing
>> > each day when the user fetches the result using the below query. So my
>> > question was , if there is any issue with the way we are fetching the
>> data
>> > and it's making some date/time shift which is why the transaction count
>> > looks to be changing even for the past days data?
>>
>> Well, your cutoff time "CURRENT_TIMESTAMP - INTERVAL '10 day'" is
>> constantly moving, so that'd account for shifts in what's perceived
>> to belong to the oldest day.  Maybe you want "CURRENT_DATE - 10"
>> instead?
>>
>> > And also somehow this
>> > conversion function "DATE_TRUNC('hour', create_timestamp AT TIME ZONE
>> > 'EST')" is showing time in CST but not EST, why so?
>>
>> 'EST' is going to rotate to UTC-5, but that's probably not what
>> you want in the summer.  I'd suggest AT TIME ZONE 'America/New_York'
>> or the like.  See
>>
>>
>> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
>>
>> regards, tom lane
>>
>
>
>  Thank you so much. You are correct. The AT TIME ZONE 'America/New_York'
> is giving correct EST time conversion.
>
> But I think regarding why it looks to be shifting i.e. the same time
> duration appears to be holding a different count of transactions while the
> base table is not getting updated/inserted/deleted for its historical
> create_timestamps, I suspect the below conversion part.
>
> The task is to count each ~15minutes duration transaction and publish in
> ordered fashion i.e. something as below, but the way it's been written
> seems wrong. It's an existing script. It first gets the date component with
> truncated hour and then adds the time component to it to make it ~15minutes
> interval. Can it be written in some simple way?
>
> 9-apr-2024 14:00 12340
> 9-apr-2024 14:15 12312
> 9-apr-2024 14:30 12323
> 9-apr-2024 14:45 12304
>
> *DATE_TRUNC('hour', create_timestamp AT TIME ZONE '*America/New_York'*) +*
> *(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE '*America/New_York*')
> / 15 * 15) * INTERVAL '15 minute'*
>

Re: Issue with date/timezone conversion function

[Adrian Klaver]

On 4/9/24 11:24, Lok P wrote:


On Tue, Apr 9, 2024 at 10:33 PM Tom Lane > wrote:




'EST' is going to rotate to UTC-5, but that's probably not what
you want in the summer.  I'd suggest AT TIME ZONE 'America/New_York'
or the like.  See

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES 


                         regards, tom lane



  Thank you so much. You are correct. The AT TIME ZONE 
'America/New_York' is giving correct EST time conversion.


But I think regarding why it looks to be shifting i.e. the same time 
duration appears to be holding a different count of transactions while 
the base table is not getting updated/inserted/deleted for its 
historical create_timestamps, I suspect the below conversion part.


The task is to count each ~15minutes duration transaction and publish in 
ordered fashion i.e. something as below, but the way it's been written 
seems wrong. It's an existing script. It first gets the date component 
with truncated hour and then adds the time component to it to make it 
~15minutes interval. Can it be written in some simple way?


9-apr-2024 14:00     12340
9-apr-2024 14:15     12312
9-apr-2024 14:30     12323
9-apr-2024 14:45     12304

/DATE_TRUNC('hour', create_timestamp AT TIME ZONE '/America/New_York'/) +/
/(EXTRACT(MINUTE FROM create_timestamp AT TIME ZONE 
'/America/New_York/') / 15 * 15) * INTERVAL '15 minute'/


Something like?:

create table dt_bin_test(id integer, tz_fld timestamptz);

insert into dt_bin_test values(1, '2024-04-01 8:15'), (2, '2024-04-01 
9:01'), (3, '2024-04-01 9:16'), (4, '2024-04-01 9:45'), (5, '2024-04-01 
8:15'), (6, '2024-04-01 9:01');


select count(tz_fld), date_bin('15 minutes', tz_fld, '2024-01-01') as 
bin from dt_bin_test group by date_bin('15 minutes', tz_fld, '2024-01-01');


 count |  bin
---+
 2 | 2024-04-01 09:00:00-07
 2 | 2024-04-01 08:15:00-07
 1 | 2024-04-01 09:15:00-07
 1 | 2024-04-01 09:45:00-07



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Storing and comparing columns of cryptographic hashes?

[Justin]

On Mon, Apr 8, 2024 at 10:08 AM Josh Triplett  wrote:

>
>
> - Is there a way to tell postgresql "this column contains cryptographic
>   hashes, so you can do hash joins using any subset of the bits, without
>   having to hash them again"? If not, should there be?
>

if you know the specific subset of the bits from the hash value ahead of
time, you can create an index on a  function to extract the subset of bits
ahead of time by putting them in an index.  Consider

create table dd2 (id integer, hash_b bytea);  --create example table
insert into dd2 values (1, '/x1234568'::bytea);  --throw a record at it
create index on dd2 (substr ( hash_b,1,5));  -- create btree index on
subset of value
select * from dd2 WHERE substr(hash_b,1,5) = '/x123';  --this query will
use the index once there enough data to justify an index scan


> - Is `bit(256)` the right type to use to store 32-byte hash values with
>   no additional overhead?
>

Keep them in the bytea format,  can easily cast from bytea to hex for
debugging purposes.

select encode(hash_b, 'hex') from dd2

The Larger the data size the slower all operations are best to keep them in
same data type instead of casting in and out different data types


> - What would be the simplest way, given an input array of hashes (which
>   I may have to pass in as an array and use `unnest`), to filter out all
>   the values that already exist, *and* generate a corresponding bitmap
>   in the same order for present/not-present for the entire array (to
>   send back to the user)? Filtering seems easy enough, but generating
>   the bitmap less so.
>

If you have unique index on the bytea hash,  you can unnest the array to a
table, then insert values with ON CONFLICT

CREATE unique index on dd2(hash_b);
INSERT into dd2 (hash_b)
(SELECT * from unnest(array['/x1234568','/x12345','/x12346', '/x12347',
'/x12348' ]::bytea[]))
ON CONFLICT (hash_b) DO NOTHING
RETURNING hash_b  -- this will  return the rows that got inserted

Then you can compare the inserted vs the inputted array to know what is
present vs not present.


> - Does it make more sense to store the values as one row per value, or
>   as one row per group of values? I know that postgresql can store an
>   entire array in one column; could that efficiently support operations
>   like "tell me which of these objects don't exist in any array in this
>   column" or "for all of these objects, tell me all the group-id values
>   for rows containing them"?
>

Putting them into arrays to keep them grouped together adds additional
overhead to unnest for searching or join operations.  If the data is going
to be large and have to scan over large data sets with  arrays containing
many hash values thats allot of data processing.  Arrays can be indexed but
have to use GIN index, which has many drawbacks compared to btree.  None of
the above queries are possible with GIN indexes or using array
columns without a lot more code.

Arrays are not data sets if  the design needs to access a specific  hash
value for update,delete, append new values, an array probably not the best
solution.
Hope this helps
Justin