Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Laurenz Albe]

On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:
> I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like 
> to change the
> {my_expression} part. After reading the documentation for ALTER TABLE
> (https://www.postgresql.org/docs/current/sql-altertable.html) and trying a 
> few things that
> resulted in syntax errors, there doesn’t seem to be a way to alter the 
> column’s GENERATED
> expression in place. It seems like my only option is to drop and re-add the 
> column.
> Is that correct?

I think that is correct.  But changing the expression would mean rewriting the 
column
anyway.  The only downside is that a dropped column remains in the table, and 
no even
a VACUUM (FULL) will get rid of it.

Yours,
Laurenz Albe

Re: Logical Replication - "invalid ordering of speculative insertion changes"

[Rahila Syed]

Hi Joe,


On Fri, Feb 3, 2023 at 1:42 AM Joe Wildish  wrote:

> Just a bump on this --- perhaps the error is a bug with the DBMS?
>
> From what I can see "speculative insertion changes" in this context means
> INSERT..ON CONFLICT DML.  Although I have some experience writing
> extensions and simple patches for the code base, I don't know anything as a
> developer about the transaction log.  I dug around a bit and it appears
> that there is a specific record type inside the WAL that differentiates
> INSERT from INSERT..ON CONFLICT changes, and it is these changes that
> cannot be re-ordered when trying to emit the message to the output plugin
> for the logical replication slot (which in this case is the internal
> pgoutput one).  Given that, I don't see how it can be user error.  Unless
> anyone else knows differently?
>
> It will be useful if you could provide steps to reproduce this issue.


Thank you,
Rahila Syed


> -Joe
>
> On Tue, 31 Jan 2023, at 18:10, Joe Wildish wrote:
> > Hello,
> >
> > We have a logical replication publisher (13.7) and subscriber (14.6)
> > where we are seeing the following error on the subscriber. IP address
> > and publication name changed, otherwise verbatim:
> >
> > 2023-01-31 15:24:49 UTC:x.x.x.x(56276):super@pubdb:[1040971]: WARNING:
> > tables were not subscribed, you will have to run ALTER SUBSCRIPTION ...
> > REFRESH PUBLICATION to subscribe the tables
> > 2023-01-31 15:24:50 UTC::@:[1040975]: LOG:  logical replication apply
> > worker for subscription "pub" has started
> > 2023-01-31 15:24:50 UTC::@:[1040975]: ERROR:  could not receive data
> > from WAL stream: ERROR:  invalid ordering of speculative insertion
> > changes
> >
> > This error occurs during the initial set up of the subscription.  We
> > hit REFRESH, and then immediately it goes into this error state. It
> > then repeats as it is retrying from here onwards and keeps hitting the
> > same error.
> >
> > My understanding is that the subscriber is performing some kind of
> > reordering of the events contained within the WAL message. As it cannot
> > then consume the message, it aborts, retries, and gets the same message
> > and errors again.  Looking in the source code it seems there is only
> > one place where this error can be emitted --- reorderbuffer.c:2179.
> > Moreover I can't tell if this is an error that I can be expected to
> > recover from as a user.
> >
> > We see this error only sometimes. Other times, we REFRESH the
> > subscription and it makes progress as one would expect.
> >
> > Can anyone advise on what we are doing wrong here?
> >
> > -Joe
>
>
>

Re: Too coarse predicate locks granularity for B+ tree indexes

[Laurenz Albe]

On Tue, 2023-02-07 at 16:23 +0600, Rinat Shigapov wrote:
> I have a concurrent testsuite that runs 14 test cases. Each test case operates
> on a disjoint set of records, doesn't retry transactions and is run under
> 'serializable' isolation level. The test data is small and likely fits within
> a single tuple page.
> 
> When I finished the test suite I was surprised that PostgreSQL 14.5 returns
> serialization failure on every test suite run.

This is no question for the hackers list; redirecting to general.

That behavior sounds perfectly normal to me: if everything is in a single
page, PostgreSQL probably won't use an index scan.  With a sequential scan,
the predicate lock will be on the whole table.  So you should expect
serialization failures.  This is well documented.

Perhaps you should use a more realistic test case with a reasonable
amount of data.

Yours,
Laurenz Albe

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

[Sebastien Flaesch]

Just sharing my conclusion so far, maybe this could do the job?

Check the usage of double quotes, to manage upper/lower case in schema and 
table name.
This is not needed for the column name, according to the doc of 
pg_get_serial_sequence().


with table_sequence as (
select '"'||ns.nspname||'"."'||p.relname||'"' tabname, c.attname colname
  from pg_class p
   join pg_attribute c on p.oid=c.attrelid
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and c.attidentity = 'd'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema())
)
select tabname, colname, pg_get_serial_sequence(tabname,colname)
  from table_sequence;


Any better suggestion?

Seb

From: Sebastien Flaesch 
Sent: Monday, February 6, 2023 7:11 PM
To: Thomas Kellerer ; pgsql-general@lists.postgresql.org 

Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY 
DEFAULT } AS IDENTITY column


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

... and I would like to automatically return the last generated identity, and 
also reset the sequence, if a value was specified explicitly for the identity 
column and this value is greater than the last generated value.

With serial/bigserial/smallserial I am adding a returning clause to each INSERT:

insert into tab1 (name) VALUES ('aaa')
  returning tab1.pkey,
 (select case when tab1.pkey > (select last_value from 
public.tab1_pkey_seq)
then setval('public.tab1_pkey_seq',tab1.pkey,true) else 0 end)

Seb

From: Sebastien Flaesch 
Sent: Monday, February 6, 2023 7:03 PM
To: Thomas Kellerer ; pgsql-general@lists.postgresql.org 

Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY 
DEFAULT } AS IDENTITY column


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

I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from 
pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 
'nextval.''([^'']*)') seqname, c.attname
  from pg_class p
   join pg_attrdef a on (p.oid=a.adrelid)
   join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and pg_get_expr(a.adbin,0) like 'nextval%'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb


From: Thomas Kellerer 
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY 
DEFAULT } AS IDENTITY column

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

Sebastien Flaesch schrieb am 06.02.2023 um 18:17:
> Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY 
> DEFAULT } AS IDENTITY|
>
> Is there any built-in function that returns the underlying sequence name used 
> for such column?
>
> Otherwise, an SQL query to return the sequence name?
>
> I need the sequence name, in order to reset it (setval) or to get the last 
> generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

PostgreSQL

[Joseph Kennedy]

I would like restrict access to sensitive or restricted information for some 
users (eg. hide data of one or more clients for some database users).

PostgreSQL allows to create security policy as Row-Level Security, policies 
based on the querying user.


Are there any other alternatives to RLS, are there any better solutions??

What are  the advantages and disadvantages of RLS?

How much RLS will cause a decrease in database performance?

JK

Re: Too coarse predicate locks granularity for B+ tree indexes

[Rinat Shigapov]

Thank you for your prompt reply!

I've mentioned that I've generated ballast data to make the cost optimizer
to switch to page-level locks.

But my question is about more finer grained (less then page) predicate
locks for indices. With page-level locks I could still get serialization
failures if I add more queries (or emulate it with sleeps) to the
transaction with the UPDATE Users query.

Below I describe the problem again for psql-general:

I have a concurrent testsuite that runs 14 test cases. Each test case
operates on a disjoint set of records, doesn't retry transactions and is
run under 'serializable' isolation level. The test data is small and likely
fits within a single tuple page.

When I finished the test suite I was surprised that PostgreSQL 14.5 returns
serialization failure on every test suite run. I was even more surprised
when I tested the suite against the current CockroachDB and didn't get
serialization failures. Actually I was able to reproduce RETRY_SERIALIZABLE
errors a couple of times on CockroachDB but it required me to run the test
suite in a loop for more than a half hour.

I started to investigate the test behavior with PostgreSQL with more
simplified and shrinked code and found a serialization failure of two
concurrent `update_user` operations.

The test defines the following `Users` table:

CREATE TABLE Users (
> id UUID,
> title VARCHAR(255),
> first_name VARCHAR(40),
> last_name VARCHAR(80) NOT NULL,
> email VARCHAR(255) NOT NULL,
> lower_email VARCHAR(255) GENERATED ALWAYS AS (lower(email)) STORED,
> marketing_optin BOOLEAN,
> mobile_phone VARCHAR(50),
> phone VARCHAR(50),
> phone_ext VARCHAR(40),
> is_contact BOOLEAN DEFAULT false NOT NULL,
> unlinked_link_ids UUID[],


> CONSTRAINT unique_user_email UNIQUE(lower_email),
> PRIMARY KEY (id)
> );


Concurrent `update_user` operation run the UPDATE query to change user
email to a unique value

UPDATE Users
> SET
> title = CASE WHEN false= true THEN 'foo' ELSE title END,
> first_name = CASE WHEN false= true THEN 'foo' ELSE first_name END,
> last_name = CASE WHEN false= true THEN 'foo' ELSE last_name END,
> email = CASE WHEN true = true THEN 'email2' ELSE email END,
> marketing_optin = CASE WHEN false = true THEN true ELSE
> marketing_optin END,
> mobile_phone = CASE WHEN false = true THEN 'foo' ELSE mobile_phone END,
> phone = CASE WHEN false = true THEN 'foo' ELSE phone END,
> phone_ext = CASE WHEN false = true THEN 'foo' ELSE phone_ext END
> WHERE id = '018629fd-7b28-743c-8647-b6321c166d46';
>

I use the following helper view to monitor locks:

> CREATE VIEW locks_v AS
> SELECT pid,
> virtualtransaction,
>locktype,
>CASE locktype
>  WHEN 'relation' THEN relation::regclass::text
>  WHEN 'virtualxid' THEN virtualxid::text
>  WHEN 'transactionid' THEN transactionid::text
>  WHEN 'tuple' THEN
> relation::regclass::text||':'||page::text||':'||tuple::text
>  WHEN 'page' THEN relation::regclass::text||':'||page::text
>END AS lockid,
>mode,
>granted
> FROM pg_locks;


 When the test Users table has only a few records the query uses a
sequential scan the serialization failure is reproducible without inserting
sleeps before `update_user` transaction commit.

This is caused by relation level predicate locks on Users table:

> select * from locks_v;
>  pid  | virtualtransaction |   locktype|  lockid   |
> mode   | granted
>
> --++---+---+--+-
>  3676 | 5/2444 | relation  | unique_user_email |
> RowExclusiveLock | t
>  3676 | 5/2444 | relation  | users_pkey|
> RowExclusiveLock | t
>  3676 | 5/2444 | relation  | users |
> RowExclusiveLock | t
>  3676 | 5/2444 | virtualxid| 5/2444|
> ExclusiveLock| t
>  3737 | 4/13470| relation  | pg_locks  |
> AccessShareLock  | t
>  3737 | 4/13470| relation  | locks_v   |
> AccessShareLock  | t
>  3737 | 4/13470| virtualxid| 4/13470   |
> ExclusiveLock| t
>  3669 | 3/17334| relation  | unique_user_email |
> RowExclusiveLock | t
>  3669 | 3/17334| relation  | users_pkey|
> RowExclusiveLock | t
>  3669 | 3/17334| relation  | users |
> RowExclusiveLock | t
>  3669 | 3/17334| virtualxid| 3/17334   |
> ExclusiveLock| t
>  3676 | 5/2444 | transactionid | 6571  |
> ExclusiveLock| t
>  3669 | 3/17334| transactionid | 6570  |
> ExclusiveLock| t
>  3676 | 5/2444 | relation  | users |
> SIReadLock   | t
>  3669 | 3/17334| relation  | users |
> SIReadLock   | t
> (15 rows

Re: PostgreSQL

[Thomas Kellerer]

Joseph Kennedy schrieb am 07.02.2023 um 12:02:
> I would like restrict access to sensitive or restricted information
> for some users (eg. hide data of one or more clients for some
> database users).
>
> PostgreSQL allows to create security policy as Row-Level Security,
> policies based on the querying user.
>
>
> Are there any other alternatives to RLS, are there any better
> solutions??

You can create a view layer and only grant access to the data through views
(and obviously revoke access to the tables directly)

Or allow access only through (set returning) functions

Re: PostgreSQL

[Giovanni Biscontini]

Hi Joseph,
   one way to filter without RLS is using functions to make queries:
this way you can cross check input parameters with login user (= user
issuing query). Using functions slow an execution but incapsulate query
letting you be able even to parse input parameters and filter results.
Sorry but I have no time ref for RLS, and remember that before you can use
RLS you must enable it on the table you want to use in.
best regards, Giovanni

Il giorno mar 7 feb 2023 alle ore 12:02 Joseph Kennedy <
joseph.kennedy@gmail.com> ha scritto:

> I would like restrict access to sensitive or restricted information for
> some users (eg. hide data of one or more clients for some database users).
>
> PostgreSQL allows to create security policy as Row-Level Security,
> policies based on the querying user.
>
>
> Are there any other alternatives to RLS, are there any better solutions??
>
> What are  the advantages and disadvantages of RLS?
>
> How much RLS will cause a decrease in database performance?
>
> JK
>
>
>
>
>

-- 





*Cordiali Saluti*






 *Dott. Giovanni Biscontini*

* [Divisone Software]*




*Str. Ponte Alto Sud, 74   41123 Modena  (MO)*

 Phone: 059_452094
 Fax: 059_8672171
 E-mail: biscontin...@es2000.it

 Skype: g.biscontini.eurosystem2000

 WEB: https://www.es2000.it 









Privacy e riservatezza: il presente messaggio, così come i relativi
allegati, contengono dati ed informazioni da considerarsi strettamente
riservate ed è indirizzato esclusivamente al destinatario sopra indicato,
il quale è l'unico autorizzato a trattarlo in osservanza delle norme del
Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo
messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a
terzi e di dare notizia al mittente dell’errato invio, distruggendone poi
l'eventuale copia cartacea e la copia in formato elettronico.

Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del
suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento,
opposizione al trattamento e cancellazione

*Please, print this e-mail only if necessary*

Re: PostgreSQL

[Joseph Kennedy]

RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or can affects to operations on whole database, table like vacuum or reindex etc ?Wiadomość napisana przez Giovanni Biscontini  w dniu 07.02.2023, o godz. 12:16:Hi Joseph,   one way to filter without RLS is using functions to make queries:this way you can cross check input parameters with login user (= user issuing query). Using functions slow an execution but incapsulate query letting you be able even to parse input parameters and filter results. Sorry but I have no time ref for RLS, and remember that before you can use RLS you must enable it on the table you want to use in.best regards, GiovanniIl giorno mar 7 feb 2023 alle ore 12:02 Joseph Kennedy  ha scritto:I would like restrict access to sensitive or restricted information for some users (eg. hide data of one or more clients for some database users).

PostgreSQL allows to create security policy as Row-Level Security, policies based on the querying user.


Are there any other alternatives to RLS, are there any better solutions??

What are  the advantages and disadvantages of RLS?

How much RLS will cause a decrease in database performance?

JK




--  

 

Cordiali Saluti

 



 

 Dott. Giovanni Biscontini

 [Divisone
Software]

 

 Str. Ponte Alto Sud, 74 
 41123 Modena  (MO)

 Phone:
059_452094 
 Fax: 059_8672171
 E-mail: biscontin...@es2000.it

 Skype: g.biscontini.eurosystem2000

 WEB: https://www.es2000.it

 

 

 



Privacy e
riservatezza: il presente messaggio, così come i relativi allegati, contengono
dati ed informazioni da considerarsi strettamente riservate ed è indirizzato
esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato a
trattarlo in osservanza delle norme del Regolamento UE 2016/679 (RGPD) .
Preghiamo chiunque ricevesse questo messaggio per errore di evitare di
copiarlo, divulgarlo, distribuirlo a terzi e di dare notizia al mittente
dell’errato invio, distruggendone poi l'eventuale copia cartacea e la copia in
formato elettronico.

Il titolare dei dati potrà esercitare tutti i
diritti di cui all'art.7 del suddetto decreto tra cui quelli di accesso,
rettifica, aggiornamento, opposizione al trattamento e cancellazione

Please, print this
e-mail only if necessary

ERROR: deadlock detected

[Matthias Apitz]

We saw the following message in the file postgres-serverlog.error:

2023-01-24 17:16:16.578 CET [17468] ERROR:  deadlock detected
2023-01-24 17:16:16.578 CET [17468] DETAIL:  Process 17468 waits for ShareLock 
on transaction 90776649; blocked by process 17724.
Process 17724 waits for ShareLock on transaction 90776650; blocked by 
process 17468.
Process 17468: fetch hc_d03geb
Process 17724: fetch hc_d02ben
2023-01-24 17:16:16.578 CET [17468] HINT:  See server log for query details.
2023-01-24 17:16:16.578 CET [17468] CONTEXT:  while locking tuple (948,45) in 
relation "d03geb"
2023-01-24 17:16:16.578 CET [17468] STATEMENT:  fetch hc_d03geb

There are no messages in the serverlog itself and in our application
log files. What can we do to get to more information (may be next time)?

The process numbers are internal ones of the PostgreSQL server and not
the Linux PID, correct?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: ERROR: deadlock detected

[Torsten Förtsch]

On Tue, Feb 7, 2023 at 12:47 PM Matthias Apitz  wrote:

>
>
> We saw the following message in the file postgres-serverlog.error:
>
> 2023-01-24 17:16:16.578 CET [17468] ERROR:  deadlock detected
> 2023-01-24 17:16:16.578 CET [17468] DETAIL:  Process 17468 waits for
> ShareLock on transaction 90776649; blocked by process 17724.
> Process 17724 waits for ShareLock on transaction 90776650; blocked
> by process 17468.
> Process 17468: fetch hc_d03geb
> Process 17724: fetch hc_d02ben
> 2023-01-24 17:16:16.578 CET [17468] HINT:  See server log for query
> details.
> 2023-01-24 17:16:16.578 CET [17468] CONTEXT:  while locking tuple (948,45)
> in relation "d03geb"
> 2023-01-24 17:16:16.578 CET [17468] STATEMENT:  fetch hc_d03geb
>
> The process numbers are internal ones of the PostgreSQL server and not
> the Linux PID, correct?
>

If you run this on linux, then the process numbers are the PIDs of the
postgres backend processes. They are not the PIDs of your application but
what would be returned by `pg_backend_pid()` or what you see in
`pg_stat_activity`.

This piece (948,45) is the CTID of the tuple where the deadlock occurred.

If you really want to find out how the deadlock came about, you could use
pg_waldump. You search for the transaction ids and figure out what they
were doing.

Torsten

Re: PostgreSQL

[Giovanni Biscontini]

Hi,
RLS rely on Create policy command:
https://www.postgresql.org/docs/15/sql-createpolicy.html
it need a table_name, only


Il giorno mar 7 feb 2023 alle ore 12:37 Joseph Kennedy <
joseph.kennedy@gmail.com> ha scritto:

> RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or
> can affects to operations on whole database, table like vacuum or reindex
> etc ?
>
> Wiadomość napisana przez Giovanni Biscontini 
> w dniu 07.02.2023, o godz. 12:16:
>
> 
> Hi Joseph,
>one way to filter without RLS is using functions to make queries:
> this way you can cross check input parameters with login user (= user
> issuing query). Using functions slow an execution but incapsulate query
> letting you be able even to parse input parameters and filter results.
> Sorry but I have no time ref for RLS, and remember that before you can use
> RLS you must enable it on the table you want to use in.
> best regards, Giovanni
>
> Il giorno mar 7 feb 2023 alle ore 12:02 Joseph Kennedy <
> joseph.kennedy@gmail.com> ha scritto:
>
>> I would like restrict access to sensitive or restricted information for
>> some users (eg. hide data of one or more clients for some database users).
>>
>> PostgreSQL allows to create security policy as Row-Level Security,
>> policies based on the querying user.
>>
>>
>> Are there any other alternatives to RLS, are there any better solutions??
>>
>> What are  the advantages and disadvantages of RLS?
>>
>> How much RLS will cause a decrease in database performance?
>>
>> JK
>>
>>
>>
>>
>>
>
>

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Philip Semanchuk]

> On Feb 7, 2023, at 3:30 AM, Laurenz Albe  wrote:
> 
> On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:
>> I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like 
>> to change the
>> {my_expression} part. After reading the documentation for ALTER TABLE
>> (https://www.postgresql.org/docs/current/sql-altertable.html) and trying a 
>> few things that
>> resulted in syntax errors, there doesn’t seem to be a way to alter the 
>> column’s GENERATED
>> expression in place. It seems like my only option is to drop and re-add the 
>> column.
>> Is that correct?
> 
> I think that is correct.  But changing the expression would mean rewriting 
> the column
> anyway.  The only downside is that a dropped column remains in the table, and 
> no even
> a VACUUM (FULL) will get rid of it.

Thanks for the confirmation. I hadn’t realized that the column would remain in 
the table even after a DROP + VACUUM FULL. I’m curious — its presence as a 
deleted column doesn't  affect performance in any meaningful way, does it? 

In this case we have the option of dropping and re-creating the table entirely, 
and that's probably what I'll do.

Cheers
Philip

Re: PostgreSQL

[Laurenz Albe]

On Tue, 2023-02-07 at 12:36 +0100, Joseph Kennedy wrote:
> RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or
> can affects to operations on whole database, table like vacuum or reindex etc 
> ?

No, that wouldn't make any sense.  Row Level Security determines who
is allowed to see or create which data.  Jobs like removing dead tuples
or rebuilding an index have nothing to do with that.

Besides, anything that requires the table owner or a superuser cannot
be reasonably controlled with row level security, because both the
table owner and a superuser are exempt from RLS anyway.

Yours,
Laurenz Albe

Re: ERROR: deadlock detected

[Laurenz Albe]

On Tue, 2023-02-07 at 12:46 +0100, Matthias Apitz wrote:
> We saw the following message in the file postgres-serverlog.error:
> 
> 2023-01-24 17:16:16.578 CET [17468] ERROR:  deadlock detected
> 2023-01-24 17:16:16.578 CET [17468] DETAIL:  Process 17468 waits for 
> ShareLock on transaction 90776649; blocked by process 17724.
> Process 17724 waits for ShareLock on transaction 90776650; blocked by 
> process 17468.
> Process 17468: fetch hc_d03geb
> Process 17724: fetch hc_d02ben
> 2023-01-24 17:16:16.578 CET [17468] HINT:  See server log for query details.
> 2023-01-24 17:16:16.578 CET [17468] CONTEXT:  while locking tuple (948,45) in 
> relation "d03geb"
> 2023-01-24 17:16:16.578 CET [17468] STATEMENT:  fetch hc_d03geb
> 
> There are no messages in the serverlog itself and in our application
> log files. What can we do to get to more information (may be next time)?

There will be a message in the server log, unless you set "log_min_messages"
to "fatal" or higher.

> The process numbers are internal ones of the PostgreSQL server and not
> the Linux PID, correct?

PostgreSQL uses the process number of the backend process on the database
server as identifier of a session, so it is indeed an operatin system process 
ID.

Yours,
Laurenz Albe

Re: PostgreSQL

[Joseph Kennedy]

Thank you Laurenz, I just wanted to make sure. 

Do you know any alternative solution to RLS ?

> Wiadomość napisana przez Laurenz Albe  w dniu 
> 07.02.2023, o godz. 15:34:
> 
> On Tue, 2023-02-07 at 12:36 +0100, Joseph Kennedy wrote:
>> RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or
>> can affects to operations on whole database, table like vacuum or reindex 
>> etc ?
> 
> No, that wouldn't make any sense.  Row Level Security determines who
> is allowed to see or create which data.  Jobs like removing dead tuples
> or rebuilding an index have nothing to do with that.
> 
> Besides, anything that requires the table owner or a superuser cannot
> be reasonably controlled with row level security, because both the
> table owner and a superuser are exempt from RLS anyway.
> 
> Yours,
> Laurenz Albe

Re: Sequence vs UUID

[Merlin Moncure]

On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer  wrote:

> On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
> > I don't really understand what you mean by 'performance'. To me it is not
> > surprising that incrementing (I know it is not just incrementing) a
> > 64bit integer is faster than generating 128 bit data with a good amount
> of
> > random data even if it seems to be too slow.
>
> But UUIDs are random and that plays havoc with locality. For example
> consider one table with invoices and another with invoice items. If you
> want to get all the invoices including the items of a single day, the
> data is probably nicely clustered together in the tables. But the join
> needs to look up random ids in the index, which will be spread all over
> the index. In a simple benchmark for this scenario the UUIDs were about
> 4.5 times slower than sequential ids. (In other benchmarks the
> difference was only a few percent)


This is really key.

While many of the people posting here may understand this, all of the
databases I've seen that are written with the UUID pattern appear to be
written by developers oblivious to this fact.  The UUID pattern seems to be
popular with developers who see abstract away the database underneath the
code and might use an ORM and be weaker in terms of database facing
constraint checking.  My direct observation is that these databases scale
poorly and the developers spend a lot of time building tools that fix
broken data stemming from application bugs.

I'm certain this is not the experience of everyone here.  I do however find
the counter sequence arguments to be somewhat silly; partition safe
sequence generation is simple to solve using simple methods. "ID guessing"
is not insecure along similar lines; if your application relies on id
obfuscation to be secure you might have much bigger issues to contend with
IMO.

merlin

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Adrian Klaver]

On 2/7/23 06:09, Philip Semanchuk wrote:




On Feb 7, 2023, at 3:30 AM, Laurenz Albe  wrote:

On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:

I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to 
change the
{my_expression} part. After reading the documentation for ALTER TABLE
(https://www.postgresql.org/docs/current/sql-altertable.html) and trying a few 
things that
resulted in syntax errors, there doesn’t seem to be a way to alter the column’s 
GENERATED
expression in place. It seems like my only option is to drop and re-add the 
column.
Is that correct?


I think that is correct.  But changing the expression would mean rewriting the 
column
anyway.  The only downside is that a dropped column remains in the table, and 
no even
a VACUUM (FULL) will get rid of it.


Thanks for the confirmation. I hadn’t realized that the column would remain in 
the table even after a DROP + VACUUM FULL. I’m curious — its presence as a 
deleted column doesn't  affect performance in any meaningful way, does it?


From docs:

https://www.postgresql.org/docs/current/sql-altertable.html

"The DROP COLUMN form does not physically remove the column, but simply 
makes it invisible to SQL operations. Subsequent insert and update 
operations in the table will store a null value for the column. Thus, 
dropping a column is quick but it will not immediately reduce the 
on-disk size of your table, as the space occupied by the dropped column 
is not reclaimed. The space will be reclaimed over time as existing rows 
are updated.


To force immediate reclamation of space occupied by a dropped column, 
you can execute one of the forms of ALTER TABLE that performs a rewrite 
of the whole table. This results in reconstructing each row with the 
dropped column replaced by a null value."




In this case we have the option of dropping and re-creating the table entirely, 
and that's probably what I'll do.

Cheers
Philip



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

Re: Sequence vs UUID

[Dominique Devienne]

On Tue, Feb 7, 2023 at 3:47 PM Merlin Moncure  wrote:

> On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer  wrote:
>
>> On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
>> But UUIDs are random and that plays havoc with locality.
>
>
> This is really key.  [...] the databases I've seen that are written with
> the
>
UUID pattern appear to be written by developers oblivious to this fact.
>

Well, perhaps these developers are not dealing with temporally clustered
data, like commerce related DB,
and more scientific data? In any case, this developer will definitely
investigate ULIDs, vs UUIDs, vs Ints (sequences),
based on the info from this thread. I'm aware of fragmentation issues, and
cache hit/miss issues etc, in general;
but was probably not sufficiently making the mental connection with UUIDs
and PostgreSQL. So thanks everyone.

Re: A Small psql Suggestion

[Raymond Brinzer]

I was really busy with work last week, so I didn't get around to thanking
you, depesz.  Setting d is a clever trick which hadn't occurred to me, and
it has indeed made things nicer for me.

I do think it would be a good thing to actually change in psql
nevertheless, since I think the suggested behavior is better most of the
time, especially if all or most of your tables have sequences.  The
built-in ability to work around it (to a good approximation of the desired
behavior) certainly does diminish the importance of the issue, though such
a solution won't be obvious to most people.

Again, though, my thanks.  This has been like a rough spot on the handle of
a tool:  trivial for occasional use, prone to raise a blister over
thousands of repetitions.

On Wed, Feb 1, 2023 at 11:04 AM hubert depesz lubaczewski 
wrote:

> On Tue, Jan 31, 2023 at 11:17:16AM -0500, Raymond Brinzer wrote:
> > Greetings,
> >
> > There is (for me) a small speed bump in psql.  I think it's worth
> > mentioning, minor though it is, because psql is such a polished tool
> > generally, and because it's something which affects me many, many times a
> > day.
> >
> > As it is, \d is a shortcut for \dtmvs.  What I actually want to see, on a
> > regular basis, are my relations:  \dtmv.  Most of the time, the sequences
> > are clutter.  If my habits are like most people's in this (and I suspect
> > they are), excluding sequences from \d would optimize for the common
> case.
>
> Perhaps just add this yourself?
> \set d '\\dtmv'
> and then
> :d
>
> or just bind \dtmv to some key like f1 or something like this?
>
> Best regards,
>
> depesz
>
>

-- 
Ray Brinzer

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Ron]

On 2/7/23 09:06, Adrian Klaver wrote:

On 2/7/23 06:09, Philip Semanchuk wrote:




On Feb 7, 2023, at 3:30 AM, Laurenz Albe  wrote:

On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:
I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d 
like to change the

{my_expression} part. After reading the documentation for ALTER TABLE
(https://www.postgresql.org/docs/current/sql-altertable.html) and 
trying a few things that
resulted in syntax errors, there doesn’t seem to be a way to alter the 
column’s GENERATED
expression in place. It seems like my only option is to drop and re-add 
the column.

Is that correct?


I think that is correct.  But changing the expression would mean 
rewriting the column
anyway.  The only downside is that a dropped column remains in the 
table, and no even

a VACUUM (FULL) will get rid of it.


Thanks for the confirmation. I hadn’t realized that the column would 
remain in the table even after a DROP + VACUUM FULL. I’m curious — its 
presence as a deleted column doesn't  affect performance in any 
meaningful way, does it?


From docs:

https://www.postgresql.org/docs/current/sql-altertable.html

"The DROP COLUMN form does not physically remove the column, but simply 
makes it invisible to SQL operations. Subsequent insert and update 
operations in the table will store a null value for the column. Thus, 
dropping a column is quick but it will not immediately reduce the on-disk 
size of your table, as the space occupied by the dropped column is not 
reclaimed. The space will be reclaimed over time as existing rows are 
updated.


To force immediate reclamation of space occupied by a dropped column, you 
can execute one of the forms of ALTER TABLE that performs a rewrite of the 
whole table. This results in reconstructing each row with the dropped 
column replaced by a null value."


VACUUM FULL doesn't rewrite the table?

The doc page seems to say that it does:
https://www.postgresql.org/docs/current/sql-vacuum.html
"|VACUUM FULL|rewrites the entire contents of the table into a new disk file 
with no extra space".


--
Born in Arizona, moved to Babylonia.

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Laurenz Albe]

On Tue, 2023-02-07 at 12:40 -0600, Ron wrote:
> > "The DROP COLUMN form does not physically remove the column, but simply 
> > makes it
> > invisible to SQL operations. Subsequent insert and update operations in the 
> > table
> > will store a null value for the column. Thus, dropping a column is quick 
> > but it
> > will not immediately reduce the on-disk size of your table, as the space 
> > occupied
> > by the dropped column is not reclaimed. The space will be reclaimed over 
> > time as
> > existing rows are updated. 
> > 
> > To force immediate reclamation of space occupied by a dropped column, you 
> > can
> > execute one of the forms of ALTER TABLE that performs a rewrite of the 
> > whole table.
> > This results in reconstructing each row with the dropped column replaced by 
> > a
> > null value."
>
> VACUUM FULL doesn't rewrite the table?

It rewrites the table, but it just copies rows rather than reconstructing them.

Yours,
Laurenz Albe

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Tom Lane]

Ron  writes:
> On 2/7/23 09:06, Adrian Klaver wrote:
>> To force immediate reclamation of space occupied by a dropped column, you 
>> can execute one of the forms of ALTER TABLE that performs a rewrite of the 
>> whole table. This results in reconstructing each row with the dropped 
>> column replaced by a null value."

> VACUUM FULL doesn't rewrite the table?

It moves all the tuples into a new file, but it does not rebuild
individual tuples, as would be needed to delete column values.

regards, tom lane

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Ron]

On 2/7/23 09:06, Adrian Klaver wrote:

On 2/7/23 06:09, Philip Semanchuk wrote:




On Feb 7, 2023, at 3:30 AM, Laurenz Albe  wrote:

On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:
I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d 
like to change the

{my_expression} part. After reading the documentation for ALTER TABLE
(https://www.postgresql.org/docs/current/sql-altertable.html) and 
trying a few things that
resulted in syntax errors, there doesn’t seem to be a way to alter the 
column’s GENERATED
expression in place. It seems like my only option is to drop and re-add 
the column.

Is that correct?


I think that is correct.  But changing the expression would mean 
rewriting the column
anyway.  The only downside is that a dropped column remains in the 
table, and no even

a VACUUM (FULL) will get rid of it.


Thanks for the confirmation. I hadn’t realized that the column would 
remain in the table even after a DROP + VACUUM FULL. I’m curious — its 
presence as a deleted column doesn't  affect performance in any 
meaningful way, does it?


From docs:

https://www.postgresql.org/docs/current/sql-altertable.html

"The DROP COLUMN form does not physically remove the column, but simply 
makes it invisible to SQL operations. Subsequent insert and update 
operations in the table will store a null value for the column. Thus, 
dropping a column is quick but it will not immediately reduce the on-disk 
size of your table, as the space occupied by the dropped column is not 
reclaimed. The space will be reclaimed over time as existing rows are 
updated.


To force immediate reclamation of space occupied by a dropped column, you 
can execute one of the forms of ALTER TABLE that performs a rewrite of the 
whole table. This results in reconstructing each row with the dropped 
column replaced by a null value."


VACUUM FULL doesn't rewrite the table?

The doc page seems to say that it does:
https://www.postgresql.org/docs/current/sql-vacuum.html
"|VACUUM FULL|rewrites the entire contents of the table into a new disk file 
with no extra space".


--
Born in Arizona, moved to Babylonia.

Re: ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Ron]

Ignore this...

On 2/7/23 13:05, Ron wrote:

On 2/7/23 09:06, Adrian Klaver wrote:

On 2/7/23 06:09, Philip Semanchuk wrote:




On Feb 7, 2023, at 3:30 AM, Laurenz Albe  wrote:

On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote:
I have a column defined GENERATED ALWAYS AS {my_expression} STORED. 
I’d like to change the

{my_expression} part. After reading the documentation for ALTER TABLE
(https://www.postgresql.org/docs/current/sql-altertable.html) and 
trying a few things that
resulted in syntax errors, there doesn’t seem to be a way to alter the 
column’s GENERATED
expression in place. It seems like my only option is to drop and 
re-add the column.

Is that correct?


I think that is correct.  But changing the expression would mean 
rewriting the column
anyway.  The only downside is that a dropped column remains in the 
table, and no even

a VACUUM (FULL) will get rid of it.


Thanks for the confirmation. I hadn’t realized that the column would 
remain in the table even after a DROP + VACUUM FULL. I’m curious — its 
presence as a deleted column doesn't  affect performance in any 
meaningful way, does it?


From docs:

https://www.postgresql.org/docs/current/sql-altertable.html

"The DROP COLUMN form does not physically remove the column, but simply 
makes it invisible to SQL operations. Subsequent insert and update 
operations in the table will store a null value for the column. Thus, 
dropping a column is quick but it will not immediately reduce the on-disk 
size of your table, as the space occupied by the dropped column is not 
reclaimed. The space will be reclaimed over time as existing rows are 
updated.


To force immediate reclamation of space occupied by a dropped column, you 
can execute one of the forms of ALTER TABLE that performs a rewrite of 
the whole table. This results in reconstructing each row with the dropped 
column replaced by a null value."


VACUUM FULL doesn't rewrite the table?

The doc page seems to say that it does:
https://www.postgresql.org/docs/current/sql-vacuum.html
"|VACUUM FULL|rewrites the entire contents of the table into a new disk 
file with no extra space".


--
Born in Arizona, moved to Babylonia.


--
Born in Arizona, moved to Babylonia.

Re: PostgreSQL

[Peter J. Holzer]

On 2023-02-07 12:36:57 +0100, Joseph Kennedy wrote:
> RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or can
> affects to operations on whole database, table like vacuum or reindex etc ?

"Row level security" works, as the name implies at the row level.
Tables, schemas and databases and covered by the "usual" SQL privilege
system.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: PostgreSQL

[Brad White]

I'm  setting my options for my DNS-less connection using
https://odbc.postgresql.org/docs/config.html
and
https://odbc.postgresql.org/docs/config-opt.html

I found it interesting that the sample at the bottom of the first page had
options that don't exist on the second page.
Namely, B4, B5, D4. I hope those are not important.

The second page has options that are nowhere mentioned on the first page,
and vice-versa.

"Recognize unique indexes" is there as "UniqueIndex", but no indication of
the proper keyword to include it in the connection string.

CX - Abbreviate doesn't seem to have a corresponding entry in the
descriptions on page 1, and from some of the samples I found, seems like a
bit field, but no indication of possible options to use.

And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset
query optimization, but no discussion on either page.
Same with Distributed Transaction settings.

Seems like, in an ideal world, there would be one page with the
description and that description would include the keyword and possible
options for each setting.

Thanks,
Brad.

How do a user-defined function that returns a table executes a query?

[Katsuya Okizaki]

Hi everyone,

I hope this message finds you in good health and spirits. I am reaching out
to this incredible community for some guidance and advice. I am currently
trying to understand how a user-defined function that returns a table
executes a query.

In a normal SQL, we can use the EXPLAIN command to view the execution plan.
However, in this case, I am not sure how a user-defined function work.

If anyone has faced a similar situation and found a way to view the
execution plan, I would greatly appreciate your insights and suggestions.

Thank you for taking the time to read my message. I look forward to hearing
from you.

Best regards,

Re: How do a user-defined function that returns a table executes a query?

[David G. Johnston]

On Tue, Feb 7, 2023 at 4:49 PM Katsuya Okizaki 
wrote:

> In a normal SQL, we can use the EXPLAIN command to view the execution
> plan. However, in this case, I am not sure how a user-defined function work.
>
> If anyone has faced a similar situation and found a way to view the
> execution plan, I would greatly appreciate your insights and suggestions.
>
>
https://www.postgresql.org/docs/current/auto-explain.html

David J.

Re: A Small psql Suggestion

[Brad White]

Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)

I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names are
not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this instance.

For example

Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
"].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", ,
adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE ("InsertFlag" =
166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Order
Items" does not exist at character 35

Any suggestions on where to look?

Thanks,
Brad.

Re: A Small psql Suggestion

[Adrian Klaver]

On 2/7/23 16:00, Brad White wrote:

Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)

I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names 
are not being quoted properly.
It has been my experience that Access usually does a better job at 
converting the queries than I would have expected, but not in this instance.


For example

Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable 
& "].[InsertFlag] = Null" _
     & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & 
"));", , adCmdText Or adExecuteNoRecords

Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE ("InsertFlag" 
= 166 )

Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Order 
Items" does not exist at character 35


Any suggestions on where to look?


1) Do not tack on to an existing thread, create a new post.

2) Where is the query coming from, manually created code or something 
ORM like?


3)


Thanks,
Brad.


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

Quoting issue from ODBC

[Brad White]

Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)

I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names are
not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this instance.

For example

Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
"].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", ,
adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE ("InsertFlag" =
166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Orders"
does not exist at character 35.

Looks like I have about 16 unique instances of statements not being quoted
correctly resulting in over 500 errors in the log for today.

Any suggestions on where to look?

Thanks,
Brad.

Re: PostgreSQL

[Brad White]

Sorry about the reply failure. I was trying to start a new thread and
failed.

On Tue, Feb 7, 2023 at 5:42 PM Brad White  wrote:

> I'm  setting my options for my DNS-less connection using
> https://odbc.postgresql.org/docs/config.html
> and
> https://odbc.postgresql.org/docs/config-opt.html
>
> I found it interesting that the sample at the bottom of the first page had
> options that don't exist on the second page.
> Namely, B4, B5, D4. I hope those are not important.
>
> The second page has options that are nowhere mentioned on the first page,
> and vice-versa.
>
> "Recognize unique indexes" is there as "UniqueIndex", but no indication of
> the proper keyword to include it in the connection string.
>
> CX - Abbreviate doesn't seem to have a corresponding entry in the
> descriptions on page 1, and from some of the samples I found, seems like a
> bit field, but no indication of possible options to use.
>
> And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset
> query optimization, but no discussion on either page.
> Same with Distributed Transaction settings.
>
> Seems like, in an ideal world, there would be one page with the
> description and that description would include the keyword and possible
> options for each setting.
>
> Thanks,
> Brad.
>

Re: A Small psql Suggestion

[Brad White]

1) Do not tack on to an existing thread, create a new post.
Yup. Failure on my part.
I'll repost.

Opportunity for clarification on ODBC options

[Brad White]

I'm  setting my options for my DNS-less connection using
https://odbc.postgresql.org/docs/config.html
and
https://odbc.postgresql.org/docs/config-opt.html

I found it interesting that the sample at the bottom of the first page had
options that don't exist on the second page.
Namely, B4, B5, D4. I hope those are not important.

The second page has options that are nowhere mentioned on the first page,
and vice-versa.

"Recognize unique indexes" is there as "UniqueIndex", but no indication of
the proper keyword to include it in the connection string.

CX - Abbreviate doesn't seem to have a corresponding entry in the
descriptions on page 1, and from some of the samples I found, seems like a
bit field, but no indication of possible options to use.

And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset
query optimization, but no discussion on either page.
Same with Distributed Transaction settings.

Seems like, in an ideal world, there would be one page with the
description and that description would include the keyword and possible
options for each setting.

Hope that helps,
Brad.

Re: Quoting issue from ODBC

[Adrian Klaver]

On 2/7/23 16:10, Brad White wrote:

Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)

I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names 
are not being quoted properly.
It has been my experience that Access usually does a better job at 
converting the queries than I would have expected, but not in this instance.


For example

Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable 
& "].[InsertFlag] = Null" _
     & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & 
"));", , adCmdText Or adExecuteNoRecords

Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE ("InsertFlag" 
= 166 )

Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Orders" 
does not exist at character 35.


Looks like I have about 16 unique instances of statements not being 
quoted correctly resulting in over 500 errors in the log for today.


Where these preexisting queries or where they created today?

If preexisting have there been any changes to the software stack since 
yesterday?


If new how where the queries generated?



Any suggestions on where to look?

Thanks,
Brad.


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

Re: Need help debugging slow logical replication

[Justin]

On Tue, Feb 7, 2023 at 6:38 PM sunyuc...@gmail.com 
wrote:

> Hi there,
>
> I am using PG 14.14 on both primary and secondary DB on AWS, setup
> using a logical replication, I'm having trouble with huge replication
> lag.
>
> My setup is as follows:
>
> P1 - physical - P1-R
>   | (logical)
> P2 - physical - P2-R
>
>
> The lag between P1 & P1-R , P2 & P2-R are both minimal , less than
> 10seconds , but I'm seeing several hours of lag between P1 & P2  .We
> currently have 1 pub/sub that covers about 100 tables.
>
> Here is the output from P1 showing there is a Lag of at least 80GB
> (keep growing)
>
> > SELECT now() AS CURRENT_TIME,
>slot_name,
>active,
>active_pid,confirmed_flush_lsn,
>pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> confirmed_flush_lsn)) AS diff_size,
>pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS
> diff_bytes
> FROM pg_replication_slots
> WHERE slot_type = 'logical';
> -[ RECORD 1 ]---+---
> current_time| 2023-02-07 23:26:00.733+00
> slot_name   | upgrade
> active  | t
> active_pid  | 5180
> confirmed_flush_lsn | 26B09/8C08C610
> diff_size   | 81 GB
> diff_bytes  | 86573472240
>
> Here is what pg_stat_replication shows: note that the write_lag is
> very high: we previously had to set wal_sender_timeout to 0, otherwise
> the logical replication work keep exiting and fail.
>
> > select * from pg_stat_replication;
> -[ RECORD 1 ]+--
> pid  | xxx
> usesysid | xxx
> usename  | dev
> application_name | upgrade_target
> client_addr  | 10.xxx
> client_hostname  |
> client_port  | 27404
> backend_start| 2023-02-07 23:02:39.228572+00
> backend_xmin | 2909692747
> state| catchup
> sent_lsn | 26B09/8C08C610
> write_lsn| 26B09/840514C0
> flush_lsn| 26B09/840514C0
> replay_lsn   | 26B09/840514C0
> write_lag| 00:07:03.60362
> flush_lag| 00:07:03.60362
> replay_lag   | 00:07:03.60362
> sync_priority| 0
> sync_state   | async
> reply_time   | 2023-02-07 23:22:08.245066+00
>
> So, the problem seems to be like P2 takes a very long time to apply a
> chunk,  Mow, my question is why?  From AWS performance insight, it
> looks like there is only 1 process running on P2, that is 100% blocked
> by CPU. I don't really understand why it is being blocked by the CPU,
> and any help in further debugging is much appreciated.
>
> Cheers.
>

Hi Sir,

What is the replica identity being used on the tables?  Are any of the
tables using  REPLICA IDENTITY FULL ?

How many tables are being replicated?

Fwd: Quoting issue from ODBC

[Brad White]

Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)

I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names are
not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this instance.

For example, this is the literal code in VBA

Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
"].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", ,
adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE ("InsertFlag" =
166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Orders"
does not exist at character 35.

No ORM involved.

Looks like I have about 16 unique instances of statements not being quoted
correctly resulting in over 500 errors in the log for today.

Any suggestions on where to look?

Thanks,
Brad.

Re: Quoting issue from ODBC

[David G. Johnston]

On Tue, Feb 7, 2023 at 5:20 PM Brad White  wrote:

> For example, this is the literal code in VBA
>
> Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
> "].[InsertFlag] = Null" _
> & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));",
> , adCmdText Or adExecuteNoRecords
> Note that InsertFlag is bracketed the same way in both instances.
>
> PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE ("InsertFlag" =
> 166 )
> Note that InsertFlag is quoted once but not the other time.
> Of course this gives the error: column "insertflag" of relation "Orders"
> does not exist at character 35.
>
>
Either VBA is doing some query munging or that concatenated string isn't
the one that is producing the error.  Which suggests that maybe you have a
string somewhere that is not written correctly that need to be fixed so it
doesn't produce this error.

David J.

Re: Quoting issue from ODBC

[Rob Sargent]

On 2/7/23 17:23, David G. Johnston wrote:

On Tue, Feb 7, 2023 at 5:20 PM Brad White  wrote:

For example, this is the literal code in VBA

Access: connection.Execute "UPDATE [" & strTable & "] SET [" &
strTable & "].[InsertFlag] = Null" _
    & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID &
"));", , adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE
("InsertFlag" = 166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation
"Orders" does not exist at character 35.


Either VBA is doing some query munging or that concatenated string 
isn't the one that is producing the error.  Which suggests that maybe 
you have a string somewhere that is not written correctly that need to 
be fixed so it doesn't produce this error.


David J.


Should the SET clause have a table alias at all?  I get an error with

   update foo set foo.id = something;

Re: PostgreSQL

[Stephen Frost]

Greetings,

* Joseph Kennedy (joseph.kennedy@gmail.com) wrote:
> Thank you Laurenz, I just wanted to make sure. 
> 
> Do you know any alternative solution to RLS ?

... to do what?  If you want to limit the set of rows that a given user
can see, RLS is how to do that.  If you want to limit the schemas or
tables that a user can see, that isn't possible in PG today (though
there was some interest a while back in making that work via RLS on the
catalogs which you might be able to find).

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Need help debugging slow logical replication

[sunyuc...@gmail.com]

Hi Justin:

- i checked that I have 2 tables using replication identity FULL, but
one table is empty and one table has only 1 row
- 7 tables using index
- overall I have ~100 tables in the publication:  But I do find that
some tables doesn't have PK , but using replica identity default, I'm
abit confused since the doc mentioned that that means it is using PK,
does that mean it is using FULL?

Cheers.

On Tue, Feb 7, 2023 at 4:20 PM Justin  wrote:
>
>
> On Tue, Feb 7, 2023 at 6:38 PM sunyuc...@gmail.com  
> wrote:
>>
>> Hi there,
>>
>> I am using PG 14.14 on both primary and secondary DB on AWS, setup
>> using a logical replication, I'm having trouble with huge replication
>> lag.
>>
>> My setup is as follows:
>>
>> P1 - physical - P1-R
>>   | (logical)
>> P2 - physical - P2-R
>>
>>
>> The lag between P1 & P1-R , P2 & P2-R are both minimal , less than
>> 10seconds , but I'm seeing several hours of lag between P1 & P2  .We
>> currently have 1 pub/sub that covers about 100 tables.
>>
>> Here is the output from P1 showing there is a Lag of at least 80GB
>> (keep growing)
>>
>> > SELECT now() AS CURRENT_TIME,
>>slot_name,
>>active,
>>active_pid,confirmed_flush_lsn,
>>pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
>> confirmed_flush_lsn)) AS diff_size,
>>pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS 
>> diff_bytes
>> FROM pg_replication_slots
>> WHERE slot_type = 'logical';
>> -[ RECORD 1 ]---+---
>> current_time| 2023-02-07 23:26:00.733+00
>> slot_name   | upgrade
>> active  | t
>> active_pid  | 5180
>> confirmed_flush_lsn | 26B09/8C08C610
>> diff_size   | 81 GB
>> diff_bytes  | 86573472240
>>
>> Here is what pg_stat_replication shows: note that the write_lag is
>> very high: we previously had to set wal_sender_timeout to 0, otherwise
>> the logical replication work keep exiting and fail.
>>
>> > select * from pg_stat_replication;
>> -[ RECORD 1 ]+--
>> pid  | xxx
>> usesysid | xxx
>> usename  | dev
>> application_name | upgrade_target
>> client_addr  | 10.xxx
>> client_hostname  |
>> client_port  | 27404
>> backend_start| 2023-02-07 23:02:39.228572+00
>> backend_xmin | 2909692747
>> state| catchup
>> sent_lsn | 26B09/8C08C610
>> write_lsn| 26B09/840514C0
>> flush_lsn| 26B09/840514C0
>> replay_lsn   | 26B09/840514C0
>> write_lag| 00:07:03.60362
>> flush_lag| 00:07:03.60362
>> replay_lag   | 00:07:03.60362
>> sync_priority| 0
>> sync_state   | async
>> reply_time   | 2023-02-07 23:22:08.245066+00
>>
>> So, the problem seems to be like P2 takes a very long time to apply a
>> chunk,  Mow, my question is why?  From AWS performance insight, it
>> looks like there is only 1 process running on P2, that is 100% blocked
>> by CPU. I don't really understand why it is being blocked by the CPU,
>> and any help in further debugging is much appreciated.
>>
>> Cheers.
>
>
> Hi Sir,
>
> What is the replica identity being used on the tables?  Are any of the tables 
> using  REPLICA IDENTITY FULL ?
>
> How many tables are being replicated?

Re: Need help debugging slow logical replication

[Justin]

On Tue, Feb 7, 2023 at 8:07 PM sunyuc...@gmail.com 
wrote:

> Hi Justin:
>
> - i checked that I have 2 tables using replication identity FULL, but
> one table is empty and one table has only 1 row
> - 7 tables using index
> - overall I have ~100 tables in the publication:  But I do find that
> some tables doesn't have PK , but using replica identity default, I'm
> abit confused since the doc mentioned that that means it is using PK,
> does that mean it is using FULL?
>
> Cheers.
>
> On Tue, Feb 7, 2023 at 4:20 PM Justin  wrote:
> >
> >
> > On Tue, Feb 7, 2023 at 6:38 PM sunyuc...@gmail.com 
> wrote:
> >>
> >> Hi there,
> >>
> >> I am using PG 14.14 on both primary and secondary DB on AWS, setup
> >> using a logical replication, I'm having trouble with huge replication
> >> lag.
> >>
> >> My setup is as follows:
> >>
> >> P1 - physical - P1-R
> >>   | (logical)
> >> P2 - physical - P2-R
> >>
> >>
> >> The lag between P1 & P1-R , P2 & P2-R are both minimal , less than
> >> 10seconds , but I'm seeing several hours of lag between P1 & P2  .We
> >> currently have 1 pub/sub that covers about 100 tables.
> >>
> >> Here is the output from P1 showing there is a Lag of at least 80GB
> >> (keep growing)
> >>
> >> > SELECT now() AS CURRENT_TIME,
> >>slot_name,
> >>active,
> >>active_pid,confirmed_flush_lsn,
> >>pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> >> confirmed_flush_lsn)) AS diff_size,
> >>pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS
> diff_bytes
> >> FROM pg_replication_slots
> >> WHERE slot_type = 'logical';
> >> -[ RECORD 1 ]---+---
> >> current_time| 2023-02-07 23:26:00.733+00
> >> slot_name   | upgrade
> >> active  | t
> >> active_pid  | 5180
> >> confirmed_flush_lsn | 26B09/8C08C610
> >> diff_size   | 81 GB
> >> diff_bytes  | 86573472240
> >>
> >> Here is what pg_stat_replication shows: note that the write_lag is
> >> very high: we previously had to set wal_sender_timeout to 0, otherwise
> >> the logical replication work keep exiting and fail.
> >>
> >> > select * from pg_stat_replication;
> >> -[ RECORD 1 ]+--
> >> pid  | xxx
> >> usesysid | xxx
> >> usename  | dev
> >> application_name | upgrade_target
> >> client_addr  | 10.xxx
> >> client_hostname  |
> >> client_port  | 27404
> >> backend_start| 2023-02-07 23:02:39.228572+00
> >> backend_xmin | 2909692747
> >> state| catchup
> >> sent_lsn | 26B09/8C08C610
> >> write_lsn| 26B09/840514C0
> >> flush_lsn| 26B09/840514C0
> >> replay_lsn   | 26B09/840514C0
> >> write_lag| 00:07:03.60362
> >> flush_lag| 00:07:03.60362
> >> replay_lag   | 00:07:03.60362
> >> sync_priority| 0
> >> sync_state   | async
> >> reply_time   | 2023-02-07 23:22:08.245066+00
> >>
> >> So, the problem seems to be like P2 takes a very long time to apply a
> >> chunk,  Mow, my question is why?  From AWS performance insight, it
> >> looks like there is only 1 process running on P2, that is 100% blocked
> >> by CPU. I don't really understand why it is being blocked by the CPU,
> >> and any help in further debugging is much appreciated.
> >>
> >> Cheers.
> >
> >
> > Hi Sir,
> >
> > What is the replica identity being used on the tables?  Are any of the
> tables using  REPLICA IDENTITY FULL ?
> >
> > How many tables are being replicated?
>

Setting REPLICA IDENTITY FULL causes FULL TABLE SCANS for UPDATES and
DELETES.  REPLICA IDENTITY FULL should only be used on INSERT only or
tables that see very few  updates and deletes.  REPLICA IDENTITY FULL will
cause replaying of the WAL to be very slow if there are many
updates/deletes for those tables.

REPLICA IDENTITY DEFAULT   will only use primary keys,  if the publisher
includes those tables, the subscriber when  replaying the WAL will stop
throwing an error not knowing how to replay the UPDATE/DELETE.

Logical Replication is most likely broken at this point.

I suggest stopping logical replication and correcting tables that don't
have qualifying indexes for logical replication by creating the
necessary indexes and avoid using replica identity full.  Then restart
logical replication from the beginning.

Re: Need help debugging slow logical replication

[sunyuc...@gmail.com]

Hi Justin, thanks for the response!

> REPLICA IDENTITY DEFAULT   will only use primary keys,  if the publisher  
> includes those tables, the subscriber when  replaying the WAL will stop 
> throwing an error not knowing how to replay the UPDATE/DELETE.

But I don't see any errors being thrown out in the postgresql logs?
Should I be seeing it complain there? Is postgresql falling back to
replica identity full here?

However I checked that table, it doesn't seem to be making progress at
all: so I suspect you are right that it is the problem.

> Logical Replication is most likely broken at this point.
>
> I suggest stopping logical replication and correcting tables that don't have 
> qualifying indexes for logical replication by creating the necessary indexes 
> and avoid using replica identity full.  Then restart logical replication from 
> the beginning.
>
>

Re: Quoting issue from ODBC

[Brad White]

On 2/7/2023 6:19 PM, Adrian Klaver wrote:

On 2/7/23 16:10, Brad White wrote:

Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)

I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table 
names are not being quoted properly.
It has been my experience that Access usually does a better job at 
converting the queries than I would have expected, but not in this 
instance.


For example

Access: connection.Execute "UPDATE [" & strTable & "] SET [" & 
strTable & "].[InsertFlag] = Null" _
     & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & 
"));", , adCmdText Or adExecuteNoRecords

Note that InsertFlag is bracketed the same way in both instances.

PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE 
("InsertFlag" = 166 )

Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation 
"Orders" does not exist at character 35.


Looks like I have about 16 unique instances of statements not being 
quoted correctly resulting in over 500 errors in the log for today.


Where these preexisting queries or where they created today?


These queries are decades old but I don't view this log file very often, 
so I don't know how long.


I'll review when I get back on site Thursday and see if I can find any 
users that are not getting the error or when it started.






Any suggestions on where to look?

Thanks,
Brad.



--
Quote Signature I talk with clients, find out where their pain points 
are, and solve those.

On-call IT Management for small companies and non-profits.
SCP, Inc.
bwh...@inebraska.com
402-601-7990


Quote of the Day
   There is a huge difference between fathering a child and being a 
father.

   One produces a child. The other produces an adult.
    -- John Eldredge

Re: Need help debugging slow logical replication

[sunyuc...@gmail.com]

ccing the mailist again in case someone else would have a idea how to debug:

Here is what I see in the pg_locks on subscribe at all time:

As you can see, it mostly concern following 3 tables, accounts,
ledger, pending_ledger_fees, which I have tried analyze, vacuum them
etc, none of them helped much:

I do see in the log these tables are getting autovaccumed *very*
frequently, is that a problem for logical replication?

2023-02-08 04:59:47 UTC::@:[13626]:LOG:  automatic vacuum of table
"dev.public.accounts": index scans: 0
pages: 0 removed, 71437 remain, 0 skipped due to pins, 11465 skipped frozen
tuples: 0 removed, 3590987 remain, 3522495 are dead but not yet
removable, oldest xmin: 2893907681
index scan bypassed: 4 pages from table (0.01% of total) have 5 dead
item identifiers
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 120107 hits, 0 misses, 0 dirtied
WAL usage: 1 records, 0 full page images, 708 bytes
system usage: CPU: user: 0.23 s, system: 0.00 s, elapsed: 1.01 s
2023-02-08 04:59:47 UTC::@:[13626]:LOG:  automatic vacuum of table
"dev.public.pending_fee_ledger": index scans: 0
pages: 0 removed, 199818 remain, 0 skipped due to pins, 117769 skipped frozen
tuples: 0 removed, 3670095 remain, 352 are dead but not yet
removable, oldest xmin: 2893907681
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
I/O timings: read: 0.000 ms, write: 0.000 ms
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 164212 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.27 s, system: 0.00 s, elapsed: 0.27 s

Thanks

On Tue, Feb 7, 2023 at 6:10 PM sunyuc...@gmail.com  wrote:
>
> Hi Justin, thanks for the response!
>
> > REPLICA IDENTITY DEFAULT   will only use primary keys,  if the publisher  
> > includes those tables, the subscriber when  replaying the WAL will stop 
> > throwing an error not knowing how to replay the UPDATE/DELETE.
>
> But I don't see any errors being thrown out in the postgresql logs?
> Should I be seeing it complain there? Is postgresql falling back to
> replica identity full here?
>
> However I checked that table, it doesn't seem to be making progress at
> all: so I suspect you are right that it is the problem.
>
> > Logical Replication is most likely broken at this point.
> >
> > I suggest stopping logical replication and correcting tables that don't 
> > have qualifying indexes for logical replication by creating the necessary 
> > indexes and avoid using replica identity full.  Then restart logical 
> > replication from the beginning.
> >
> >