Re: Logical replication stuck and no data being copied

[anonymous001]

After further investigation, I discovered the following.

On aurora ( the subscriber).
select now()::timestamp(0),a.subname,b.srsubstate,count(*) from
pg_subscription_rel b, pg_subscription a where  b.srsubid=a.oid group by
1,2,3 order by 2,3;

 now | subname | srsubstate | count 
-+-++---
 2021-02-18 08:53:31 | sub_prodza_big  | r  |11
 2021-02-18 08:53:31 | sub_prodza_default  | r  |   108
 2021-02-18 08:53:31 | sub_prodza_trade| r  | 1
 2021-02-18 08:53:31 | sub_prodza_tradearchive | i  | 1


For the table where the snapshot isn't being taken and the state remains in
initialize. I drop the other subscriptions and publications.

Then I get the following:
prodza=# select * from pg_replication_slots ;
-[ RECORD 1 ]---+-
slot_name   | test_slot
plugin  | test_decoding
slot_type   | logical
datoid  | 221550750
database| prodza
temporary   | f
active  | f
active_pid  | 
xmin| 
catalog_xmin| 798588827
restart_lsn | 7A7/520BAD48
confirmed_flush_lsn | 7A7/520BAD80
-[ RECORD 2 ]---+-
slot_name   | sub_prodza_tradearchive_7775427_sync_3552943
plugin  | pgoutput
slot_type   | logical
datoid  | 221550750
database| prodza
temporary   | t
active  | t
active_pid  | 25001
xmin| 
catalog_xmin| 799048174
restart_lsn | 7AC/6003D048
confirmed_flush_lsn | 7AC/6003D080
-[ RECORD 3 ]---+-
slot_name   | sub_prodza_tradearchive
plugin  | pgoutput
slot_type   | logical
datoid  | 221550750
database| prodza
temporary   | f
active  | t
active_pid  | 24835
xmin| 
catalog_xmin| 799048182
restart_lsn | 7AC/60058488
confirmed_flush_lsn | 7AC/600584C0

Now it seems that a snapshot is being taken for the table, but when I create
another publication and subscription, both are in state 'd'.
on aurora (subscriber)

prodza=> select now()::timestamp(0),a.subname,b.srsubstate,count(*) from
pg_subscription_rel b, pg_subscription a where  b.srsubid=a.oid group by
1,2,3 order by 2,3;

 now | subname | srsubstate | count 
-+-++---
 2021-02-18 09:38:24 | sub_prodza_trade| d  | 1
 2021-02-18 09:38:24 | sub_prodza_tradearchive | d  | 1

Which is not correct, since the table trade's data is already synced it
should be in srsubstate = 'r'.
Does this mean that the tradearchive has to complete before data will be
published to the subscriber for the table trade?

Also, why do the replication slots share the same restart_lsn and
confirmed_flush lsn?.

-[ RECORD 1 ]---+-
slot_name   | sub_prodza_trade
plugin  | pgoutput
slot_type   | logical
datoid  | 221550750
database| prodza
temporary   | f
active  | t
active_pid  | 25381
xmin| 
catalog_xmin| 799051115
restart_lsn | 7AC/60CD9F10
confirmed_flush_lsn | 7AC/60CD9F80
-[ RECORD 2 ]---+-
slot_name   | sub_prodza_tradearchive_7775427_sync_3552943
plugin  | pgoutput
slot_type   | logical
datoid  | 221550750
database| prodza
temporary   | t
active  | t
active_pid  | 25001
xmin| 
catalog_xmin| 799048174
restart_lsn | 7AC/6003D048
confirmed_flush_lsn | 7AC/6003D080
-[ RECORD 3 ]---+-
slot_name   | sub_prodza_tradearchive
plugin  | pgoutput
slot_type   | logical
datoid  | 221550750
database| prodza
temporary   | f
active  | t
active_pid  | 24835
xmin| 
catalog_xmin| 799051115
restart_lsn | 7AC/60CD9F10
confirmed_flush_lsn | 7AC/60CD9F80








--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

permanent setting of config variables

[Joao Miguel Ferreira]

Hi,

I have a few PL/pgSQL functions that use queires like "SHOW company.product
INTO _product_name" and "SHOW company.cluster INTO _cluster_number".

But these are failing because I don't know how to set those values on a
permanent basis, or maybe on startup would also be ok.

I did read the "ALTER DATABASE " and the "set_config(...)" documentation
and I experimented a bit with that but without succes.

So my question would be: how to permanently set user specific config values
that would become accessible to the "SHOW " SQL comand.

Thank you
Joao

Re: Slow index creation

[hubert depesz lubaczewski]

On Wed, Feb 17, 2021 at 08:40:17PM +0100, Paul van der Linden wrote:
> The st_area calculation is done mostly once or sometimes twice for each geom, 
> and I suspect that can't explain the factor 20 slower.
> Creating an index with only one st_area calculation is also done rather 
> quickly.

In this case, make small test case, like 1000 rows, or something like
this. run create index without where, and then remake the functions with
profiling info, for example using this approach:
https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
and check for yourself what takes this time, and if it's something you
can fix.

Best regards,

depesz

Re: permanent setting of config variables

[Laurenz Albe]

On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote:
> I have a few PL/pgSQL functions that use queires like "SHOW company.product 
> INTO _product_name" and "SHOW company.cluster INTO _cluster_number".
> But these are failing because I don't know how to set those values on a 
> permanent basis, or maybe on startup would also be ok.
> I did read the "ALTER DATABASE " and the "set_config(...)" documentation and 
> I experimented a bit with that but without succes.
> So my question would be: how to permanently set user specific config values 
> that would become accessible to the "SHOW " SQL comand.

_product_name := current_setting('company.product');

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

when is pg_stat_archiver.stats_reset modified?

[Luca Ferrari]

Hi all,
running on 12.5, but I guess this does not mind, I cannot understand
when the value of pg_stat_archiver.stats_reset field is modified.
I've executed a pg_stat_reset() but it did not affected the specified field.
I suppose that field is not "resettable" by the administrator, as I
tried to, but then what drives changes?

Thanks,
Luca

Re: permanent setting of config variables

[Joao Miguel Ferreira]

Hi,


On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe 
wrote:

> On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote:
> > I have a few PL/pgSQL functions that use queires like "SHOW
> company.product INTO _product_name" and "SHOW company.cluster INTO
> _cluster_number".
> > But these are failing because I don't know how to set those values on a
> permanent basis, or maybe on startup would also be ok.
> > I did read the "ALTER DATABASE " and the "set_config(...)" documentation
> and I experimented a bit with that but without succes.
> > So my question would be: how to permanently set user specific config
> values that would become accessible to the "SHOW " SQL comand.
>
> _product_name := current_setting('company.product');
>
>
I can not change the code in those functions. I need to make them work as
they are.

So I guess I need to set those variables from an administration action
rather than from a coding action.

Something like "ALTER SYSTEM SET company.procut TO 'SpaceCraft';" but this
returns an error saying "ERROR:  unrecognized configuration parameter
"company.procut". If I'm not  mistaken, the "SET " command lasts only
for the duration of a client session. I need it to be permanent.


> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: when is pg_stat_archiver.stats_reset modified?

[Ian Lawrence Barwick]

2021年2月18日(木) 20:21 Luca Ferrari :

> Hi all,
> running on 12.5, but I guess this does not mind, I cannot understand
> when the value of pg_stat_archiver.stats_reset field is modified.
> I've executed a pg_stat_reset() but it did not affected the specified
> field.
> I suppose that field is not "resettable" by the administrator, as I
> tried to, but then what drives changes?
>

As pg_stat_archiver shows cluster-wide stats, you need
pg_stat_reset_shared().

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS

Regards

Ian Barwick

-- 
EnterpriseDB: https://www.enterprisedb.com

Re: when is pg_stat_archiver.stats_reset modified?

[Laurenz Albe]

On Thu, 2021-02-18 at 12:20 +0100, Luca Ferrari wrote:
> running on 12.5, but I guess this does not mind, I cannot understand
> when the value of pg_stat_archiver.stats_reset field is modified.
> I've executed a pg_stat_reset() but it did not affected the specified field.
> I suppose that field is not "resettable" by the administrator, as I
> tried to, but then what drives changes?

SELECT pg_stat_reset_shared('archiver');

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: permanent setting of config variables

[Laurenz Albe]

On Thu, 2021-02-18 at 11:42 +, Joao Miguel Ferreira wrote:
> On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe  
> wrote:
> > On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote:
> > > I have a few PL/pgSQL functions that use queires like "SHOW 
> > > company.product INTO _product_name" and "SHOW company.cluster INTO 
> > > _cluster_number".
> > > But these are failing because I don't know how to set those values on a 
> > > permanent basis, or maybe on startup would also be ok.
> > > I did read the "ALTER DATABASE " and the "set_config(...)" documentation 
> > > and I experimented a bit with that but without succes.
> > > So my question would be: how to permanently set user specific config 
> > > values that would become accessible to the "SHOW " SQL comand.
> > 
> > _product_name := current_setting('company.product');
> > 
> 
> I can not change the code in those functions. I need to make them work as 
> they are.

That makes no sense.  You say that the statements in your functions are failing.
Then you *have* to change the code.

> So I guess I need to set those variables from an administration action rather 
> than from a coding action.

Your question was about SHOW, not SET, so I have lost you here.

> Something like "ALTER SYSTEM SET company.procut TO 'SpaceCraft';" but this 
> returns
>  an error saying "ERROR:  unrecognized configuration parameter 
> "company.procut".
>  If I'm not  mistaken, the "SET " command lasts only for the duration of a
>  client session. I need it to be permanent.

These parameters do not exist, so you cannot set them permanently.
That's the way it is.

To turn them into parameters, you would have to write a small extension in C
and load it into the server.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: permanent setting of config variables

[Joe Conway]

On 2/18/21 4:09 AM, Joao Miguel Ferreira wrote:
> I have a few PL/pgSQL functions that use queires like "SHOW company.product 
> INTO
> _product_name" and "SHOW company.cluster INTO _cluster_number".
> 
> But these are failing because I don't know how to set those values on a
> permanent basis, or maybe on startup would also be ok.
> 
> I did read the "ALTER DATABASE " and the "set_config(...)" documentation and I
> experimented a bit with that but without succes.
> 
> So my question would be: how to permanently set user specific config values 
> that
> would become accessible to the "SHOW " SQL comand.

Perhaps I don't understand your issue, but this works for me:

8<---
nmx=# alter database nmx set a.b = 'c';
ALTER DATABASE

nmx=# \q


psql nmx
psql (12.5)
Type "help" for help.

nmx=# show a.b;
 a.b
-
 c
(1 row)

CREATE OR REPLACE FUNCTION test() RETURNS text AS $$
DECLARE
 pn text;
BEGIN
 SHOW a.b INTO pn;
 RETURN pn;
END;
$$ LANGUAGE plpgsql;

nmx=# SELECT test();
 test
--
 c
(1 row)

nmx=# \q


psql nmx
psql (12.5)
Type "help" for help.

nmx=# SELECT test();
 test
--
 c
(1 row)
8<---

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: permanent setting of config variables

[Joao Miguel Ferreira]

Hi,

On Thu, Feb 18, 2021 at 4:05 PM Joe Conway  wrote:

> On 2/18/21 4:09 AM, Joao Miguel Ferreira wrote:
> > I have a few PL/pgSQL functions that use queires like "SHOW
> company.product INTO
> > _product_name" and "SHOW company.cluster INTO _cluster_number".
> >
> > But these are failing because I don't know how to set those values on a
> > permanent basis, or maybe on startup would also be ok.
> >
> > I did read the "ALTER DATABASE " and the "set_config(...)" documentation
> and I
> > experimented a bit with that but without succes.
> >
> > So my question would be: how to permanently set user specific config
> values that
> > would become accessible to the "SHOW " SQL comand.
>
> Perhaps I don't understand your issue, but this works for me:
>
> 8<---
> nmx=# alter database nmx set a.b = 'c';
> ALTER DATABASE
>
> nmx=# \q
>
>
> psql nmx
> psql (12.5)
> Type "help" for help.
>
> nmx=# show a.b;
>  a.b
> -
>  c
> (1 row)
>
>
I have just tried your example and it worked just fine.

I guess I was doing something wrong on my previous attempts.

And also, from your example, it also means that this can be achieved by the
client application, which is also cool, and better for me than the server
side approach

Thank you for the clear example
Joao


> CREATE OR REPLACE FUNCTION test() RETURNS text AS $$
> DECLARE
>  pn text;
> BEGIN
>  SHOW a.b INTO pn;
>  RETURN pn;
> END;
> $$ LANGUAGE plpgsql;
>
> nmx=# SELECT test();
>  test
> --
>  c
> (1 row)
>
> nmx=# \q
>
>
> psql nmx
> psql (12.5)
> Type "help" for help.
>
> nmx=# SELECT test();
>  test
> --
>  c
> (1 row)
> 8<---
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

yum update for postgresql rpms

[Haas, Scott]

RHEL 7.9
PostgreSQL 12 (12.5.1 to 12.6.1)

I am updating RPMs for postgres and was interested to know whether this is 
indeed expected behavior for the rpm update:

When the "yum update" is executed, postgresql rpms are updated.  It looks at 
that point, postgresql (postmaster) processes automatically restart.  In the 
procedure I was building, I figured I'd need to stop/start postgres (systemctl 
stop postgresql-12, systemctl start postgresql-12), but that doesn't look to be 
necessary.

I don't recall this automatic postmaster restart occurring a few weeks ago when 
I went from 12.4.1 to 12.5.1 - perhaps it did, but I just stopped/re-started 
postgres unnecessarily.


Thanks,
Scott

--
Scott Haas
Enterprise Applications

sah...@psu.edu
814-863-3526
https://keybase.io/sah209

Re: Slow index creation

[Michael Lewis]

>
> https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/


Thanks for this reference. I enjoy your blog, but haven't made the time to
read all the archives somehow. Stuff doesn't stick very well when it isn't
yet "needed" info besides.

I have seen overhead from 'raise notice' in small functions that are
sometimes called many thousands of times in a single query, but hadn't done
the test to verify if the same overhead still exists for raise debug or
another level below both client_min_messages and log_min_messages. Using
your examples, I saw about .006 ms for each call to RAISE DEBUG with a
client/log_min as notice/warning.

Re: Slow index creation

[hubert depesz lubaczewski]

On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote:
>   [1]https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
> 
> Thanks for this reference. I enjoy your blog, but haven't made the time to 
> read all the archives somehow. Stuff doesn't stick very
> well when it isn't yet "needed" info besides.
> I have seen overhead from 'raise notice' in small functions that are 
> sometimes called many thousands of times in a single query, but
> hadn't done the test to verify if the same overhead still exists for raise 
> debug or another level below both client_min_messages
> and log_min_messages. Using your examples, I saw about .006 ms for each call 
> to RAISE DEBUG with a client/log_min as notice/warning.

Sure, this overhead is definitely possible, but kinda besides the point
- there will be some slowdowns in other places, and it will be good to
track them.
That's why I suggested to do it on small sample of data.

Best regards,

depesz

Re: How to post to this mailing list from a web based interface

[Magnus Hagander]

On Mon, Feb 15, 2021 at 11:47 PM RaviKrishna  wrote:
>
> > Nable didn't seem to work. I got a bounce iirc.
>
> Not sure what you are doing?  My previous reply a day ago and this reply are
> from Nable with no issues.

Not wtih "no issues".

A lot of email sent from Nabble through the lists end up being spam
flagged and either ignored or blocked by individual subscribers,
because they basically fake the email sender. In this case your mail
provider (yahoo) explicitly instructs recipients to treat it as spam
when you send it through Nabble.

There's enough issues with delivery of emails posted through Nabbel
that we have many times considered simply blocking it so that people
dont' *think* it works well, when it doesn''t. (Since most of the
time, the emails end up in the spam folder, there is no way that you
as a sender end up knowing about it).

Buttom line is that while it may be a good tool for reading, it is
*not* a good tool for posting, at least not until they fix their basic
handling of email.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Error with pg_dump (of data), with --role

[Ken Tanzer]

Hi.  I'm trying to do a data dump with pg_dump using RLS and --set-role,
but am getting an error, and I'm not understanding why. With this command,
run as postgres:

pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security
--column-inserts -a -f ~/ag_tacoma_data.pg_dump ag_rcafe

I get
[Multiple notices about circular foreign keys, like this, which I don't
think are directly-relevant]
NOTICE: there are circular foreign-key constraints among these tables:
pg_dump:   tbl_client
pg_dump:   tbl_l_veteran_status
pg_dump:   tbl_staff
pg_dump: You might not be able to restore the dump without using
--disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid
this problem.

But then crash out with:

pg_dump: [archiver (db)] query failed: ERROR:  function
has_segment_access(character varying, name) does not exist
LINE 3: SELECT has_segment_access(segment,current_user);
   ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:

SELECT has_segment_access(segment,current_user);

CONTEXT:  SQL function "has_segment_access" during inlining
pg_dump: [archiver (db)] query was: DECLARE _pg_dump_cursor CURSOR FOR
SELECT * FROM ONLY public.tbl_client

Which I don't get.  That function does exist, and is callable by both
postgres and the ag_TACOMA users.

ag_rcafe=# \df has_segment_access
 List of functions
 Schema |Name| Result data type |   Argument data
types   |  Type
++--+-+
 public | has_segment_access | boolean  | segment character varying
  | normal
 public | has_segment_access | boolean  | segment character
varying, db_user name | normal
 public | has_segment_access | boolean  | segments character
varying[]| normal
(3 rows)

ag_rcafe=# SELECT current_user,has_segment_access('TACOMA',current_user);
 current_user | has_segment_access
--+
 postgres | f
(1 row)

ag_rcafe=# SET ROLE "rcafe_TACOMA";
SET
ag_rcafe=> SELECT current_user,has_segment_access('TACOMA',current_user);
 current_user | has_segment_access
--+
 rcafe_TACOMA | t
(1 row)

So if the error means what it says, I don't get why.  It would make more
sense to me if there were a restore, with an issue about how to sequence
the creation of things.  But since it's a dump, shouldn't everything just
be there?

Any help appreciated.  More info & context below.

Thanks.

Ken

version:  9.6.20
This database is about 8 years old, and has been through one if not two
upgrades, which I mention to say who knows what weirdness or cruft (or
corruption?) might have crept in.

It's a multi-tenant DB using RLS so that each tenant can only see their own
data.  One of the tenants needs to have their data created in a separate
database.  My initial take on how to do this was to dump the schema as
postgres, and then dump the data as the particular user. (ag_TACOMA).  But
I haven't gotten very far with that. :)

There is only one schema, public.


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Error with pg_dump (of data), with --role

[Rob Sargent]

On 2/18/21 6:18 PM, Ken Tanzer wrote:
Hi.  I'm trying to do a data dump with pg_dump using RLS and --set-role, 
but am getting an error, and I'm not understanding why. With this 
command, run as postgres:


pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security 
--column-inserts -a -f ~/ag_tacoma_data.pg_dump ag_rcafe


I get
[Multiple notices about circular foreign keys, like this, which I don't 
think are directly-relevant]

NOTICE: there are circular foreign-key constraints among these tables:
pg_dump:   tbl_client
pg_dump:   tbl_l_veteran_status
pg_dump:   tbl_staff
pg_dump: You might not be able to restore the dump without using 
--disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to 
avoid this problem.


But then crash out with:

pg_dump: [archiver (db)] query failed: ERROR:  function 
has_segment_access(character varying, name) does not exist

LINE 3: SELECT has_segment_access(segment,current_user);
                ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.

QUERY:

SELECT has_segment_access(segment,current_user);

CONTEXT:  SQL function "has_segment_access" during inlining
pg_dump: [archiver (db)] query was: DECLARE _pg_dump_cursor CURSOR FOR 
SELECT * FROM ONLY public.tbl_client


Which I don't get.  That function does exist, and is callable by both 
postgres and the ag_TACOMA users.


ag_rcafe=# \df has_segment_access
                                          List of functions
  Schema |        Name        | Result data type |           Argument 
data types           |  Type

++--+-+
  public | has_segment_access | boolean          | segment character 
varying               | normal
  public | has_segment_access | boolean          | segment character 
varying, db_user name | normal
  public | has_segment_access | boolean          | segments character 
varying[]            | normal

(3 rows)

ag_rcafe=# SELECT current_user,has_segment_access('TACOMA',current_user);
  current_user | has_segment_access
--+
  postgres     | f
(1 row)

ag_rcafe=# SET ROLE "rcafe_TACOMA";
SET
ag_rcafe=> SELECT current_user,has_segment_access('TACOMA',current_user);
  current_user | has_segment_access
--+
  rcafe_TACOMA | t
(1 row)

So if the error means what it says, I don't get why.  It would make more 
sense to me if there were a restore, with an issue about how to sequence 
the creation of things.  But since it's a dump, shouldn't everything 
just be there?


Any help appreciated.  More info & context below.

Thanks.

Ken

version:  9.6.20
This database is about 8 years old, and has been through one if not two 
upgrades, which I mention to say who knows what weirdness or cruft (or 
corruption?) might have crept in.


It's a multi-tenant DB using RLS so that each tenant can only see their 
own data.  One of the tenants needs to have their data created in a 
separate database.  My initial take on how to do this was to dump the 
schema as postgres, and then dump the data as the particular user. 
(ag_TACOMA).  But I haven't gotten very far with that. :)


There is only one schema, public.




I suspect it is because "set role" doesn't "set search_path"

Re: Error with pg_dump (of data), with --role

[Ken Tanzer]

On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent  wrote:

>
> >
> > There is only one schema, public.
> >
> >
>
> I suspect it is because "set role" doesn't "set search_path"
>
>
> I'm not sure what you mean or are suggesting by that.  Is there something
I'm supposed to do to set the search path?  Is that a known bug in
pg_dump?  Something else?  As mentioned, there is only one schema

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Error with pg_dump (of data), with --role

[Rob Sargent]

> On Feb 18, 2021, at 8:00 PM, Ken Tanzer  wrote:
> 
> 
> 
> 
>> On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent  wrote:
>> 
>> > 
>> > There is only one schema, public.
>> > 
>> > 
>> 
>> I suspect it is because "set role" doesn't "set search_path"
>> 
>> 
> I'm not sure what you mean or are suggesting by that.  Is there something I'm 
> supposed to do to set the search path?  Is that a known bug in pg_dump?  
> Something else?  As mentioned, there is only one schema  
> 
> -- 
Do you need to set role at all? 
Can you put the function in “public”?
> 

Re: Error with pg_dump (of data), with --role

[Tom Lane]

Ken Tanzer  writes:
> I'm not sure what you mean or are suggesting by that.  Is there something
> I'm supposed to do to set the search path?  Is that a known bug in
> pg_dump?  Something else?  As mentioned, there is only one schema

There was a security change to pg_dump a few years ago to make it
put "set search_path = pg_catalog" into the dump script.  This
basically means that any user-defined function in indexes, check
constraints, etc is on its own to be sure that it schema-qualifies
non-system names, or has a "SET search_path" clause to do that
for it.  While that's annoying, it's also good practice.  Functions
that could be invoked in these contexts really ought not assume
what search path they are called with.

I do not think any of the other details you mentioned, such as
use of --role, have any impact on this.

regards, tom lane

RE: Syntax checking DO blocks and ALTER TABLE statements?

[Kevin Brannen]

>From: Ron 
>
>How does one go about syntax checking this?
>
>do $$
>begin if exists (select 1 from information_schema.table_constraints
>where constraint_name = 'error_to_web_service_error') then
> raise notice 'EXISTS error_to_web_service_error';
> else
> ALTER TABLE web_service_error
>ADD CONSTRAINT error_to_web_service_error FOREIGN KEY (error_id)
>REFERENCES error_code(error_id)
>ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
> end if
>end $$
>
>(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping in 
>similar DO blocks, and want to make sure the statements are clean.)


I've always wondered why Pg doesn't have something like that built in, but I 
suppose the obvious answer is that no one has felt like scratching that itch.

Have you checked out:  https://github.com/okbob/plpgsql_check

I don't know if it'll do everything you want, but maybe it'd help at least 
some. It's on my to-do list to check out one day when I have time. :)

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: Syntax checking DO blocks and ALTER TABLE statements?

[Pavel Stehule]

pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen  napsal:

> >From: Ron 
> >
> >How does one go about syntax checking this?
> >
> >do $$
> >begin if exists (select 1 from information_schema.table_constraints
> >where constraint_name = 'error_to_web_service_error') then
> > raise notice 'EXISTS error_to_web_service_error';
> > else
> > ALTER TABLE web_service_error
> >ADD CONSTRAINT error_to_web_service_error FOREIGN KEY
> (error_id)
> >REFERENCES error_code(error_id)
> >ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
> > end if
> >end $$
> >
> >(There are 222 ALTER TABLE ADD FOREIGN KEY statements that I'm wrapping
> in similar DO blocks, and want to make sure the statements are clean.)
>
>
> I've always wondered why Pg doesn't have something like that built in, but
> I suppose the obvious answer is that no one has felt like scratching that
> itch.
>

plpgsql_check is my cleaned previous project plpgsql_lint.  Main target of
this work was integration to upstream. Unfortunately there is not an
agreement on how this feature should be implemented  - there is a very
fundamental difference in opinions, so this patch was rejected (I spent a
lot of time working on this patch).  On second hand - with an outer
development I had more space for faster more experimental development and I
can quickly push new features to all supported pg releases (not just to
fresh release). This is an advantage of extensions - the development can be
much faster and because plpgsql_check is well isolated (it depends mostly
only on plpgsql runtime), then the development and maintenance is not too
difficult and expensive. There is only one harder task (for me) - making
builds for MS Win.

Now plpgsql_check is a relatively bigger project - so it is hard to merge
it to upstream, but it can live well on github simillary like PostGIS.

Regards

Pavel


> Have you checked out:  https://github.com/okbob/plpgsql_check
>
> I don't know if it'll do everything you want, but maybe it'd help at least
> some. It's on my to-do list to check out one day when I have time. :)
>
> HTH,
> Kevin
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you are
> not the intended recipient, or a person responsible for delivering it to
> the intended recipient, you are hereby notified that any disclosure,
> distribution, review, copy or use of any of the information contained in or
> attached to this message is STRICTLY PROHIBITED. If you have received this
> transmission in error, please immediately notify us by reply e-mail, and
> destroy the original transmission and its attachments without reading them
> or saving them to disk. Thank you.
>