Trigger not firing

[Hans]

Hi,


I've had a weird problem in a production system. The customer had 
installed a new server with our software on it. The software installs a 
Postgres database schema that includes a number of triggers. The 
triggers perform inserts into an additional table.


In this installation, from what I can tell, some triggers somehow got 
into a disabled state:


- they were confirmed to be present (checked using pgAdmin 4).

- In the trigger property window of pgAdmin 4, the triggers were listed 
as enabled.


- However, attempts to trigger them (by performing an appropriate insert 
or update) didn't visibly result in the trigger running (no entries in 
the additional table were created). The insert/update itself worked fine.


I asked the customer to look in pg_trigger. The triggers were listed 
with tgenabled set to 'O' (but I'm not sure if that is the right thing 
to look at).


Our software contains no code for disabling triggers. It creates them 
once, during database initialisation (i.e. before any data is put in), 
and then leaves them alone. I have no reason to believe the customer 
messed with the database either.


How we resolved this: after using "triggers -> enable all" in pgAdmin on 
that table, the triggers were now found to be working as expected. So 
the trigger code is correct and works, but it (somehow) wasn't enabled. 
Moreover, pgAdmin apparently reported this state incorrectly. My 
question is: what could have happened?


Postgres 10.3, 64-bits, Ubuntu (I think 18.04).

- Are triggers always automatically created in the 'enabled' state, or 
are there conditions that could cause them to start as 'disabled', or in 
some other way inactive?


- What could have caused pgAdmin to report the trigger as 'enabled' even 
though, from what I can tell, it really wasn't?


- Is there a log file that could shed more light on this situation?

The customer is willing to try another installation, to see if the 
problem can be replicated. I can ask for additional logging to be 
enabled if that helps in understanding this problem. What would be 
helpful in troubleshooting this?



Thanks in advance for any insight you may have,

Hans

Re: Trigger not firing

[Hans]

I've had a weird problem in a production system. The customer had 
installed a new server with our software on it. The software installs 
a Postgres database schema that includes a number of triggers. The 
triggers perform inserts into an additional table.


How is the install done?


Our instructions tell them to apt-get it from the default repository. I 
can ask on tuesday for more information.


In this installation, from what I can tell, some triggers somehow got 
into a disabled state:


- they were confirmed to be present (checked using pgAdmin 4).

- In the trigger property window of pgAdmin 4, the triggers were 
listed as enabled.


When in doubt use psql to look at the table. So:

\d table_name.

That will show you the state of the triggers.


Ok, thanks.

Our software contains no code for disabling triggers. It creates them 
once, during database initialisation (i.e. before any data is put in), 
and then leaves them alone. I have no reason to believe the customer 
messed with the database either.


Exactly how is that done?


We give them a C++ program that creates the tables, and then executes:

CREATE OR REPLACE FUNCTION generic.update_usergrouptest_from_test() 
RETURNS trigger AS $$

DECLARE
 x INTEGER;
BEGIN
 IF NEW.usergroup_ids <> OLD.usergroup_ids THEN
  DELETE FROM generic.usergroup_test WHERE test_id = NEW.id;

  FOREACH x IN ARRAY NEW.usergroup_ids LOOP
   INSERT INTO generic.usergroup_test (test_id, usergroup_id) VALUES 
(NEW.id, x);

  END LOOP;
 END IF;

 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

and then

CREATE TRIGGER update_usergrouptest_from_test
AFTER UPDATE ON generic.test
FOR EACH ROW EXECUTE PROCEDURE generic.update_usergrouptest_from_test();

(we are simplifying the use of N-M relations by putting multiple foreign 
keys into an array field. The N-M table takes care of foreign key 
constraints, but is never touched by the software. The software only 
ever looks at the array field. The _SQL_ may be simple enough for N-M 
tables, but the _C++_ is really much happier if it can treat these 
foreign keys as an array, instead of an extra table. Having real arrays 
of foreign keys would be nice, but this works too).



Hans

Re: Trigger not firing

[Hans]

you trigger can be much faster if you replace FOREACH cycle by unnest

INSERT INTO generic.usergroup_test(test_id, usergroup_id) VALUES(NEW.id, 
UNNEST(NEW.usergroup_ids));


Thanks! Appreciated :-)


Hans

wiki Disk Usage, table size: ERROR: could not open relation with OID 0

[Hans Schou]

Hi

I got the message
  ERROR: could not open relation with OID 0
when running the "General Table Size Information" from
https://wiki.postgresql.org/wiki/Disk_Usage

This patch gives some system tables
@@ -12,5 +12,6 @@
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r'
+  AND reltoastrelid!=0
   ) a
 ) a;

But I guess it was supposed to give size of all tables.

I'm running version 9.1.9 so it should be working according to the wiki.

The original statement:

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS
table_bytes FROM (
  SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
  , c.reltuples AS row_estimate
  , pg_total_relation_size(c.oid) AS total_bytes
  , pg_indexes_size(c.oid) AS index_bytes
  , pg_total_relation_size(reltoastrelid) AS toast_bytes
  FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE relkind = 'r'
  ) a) a;


Any help much appreciated.

./best regards

Re: wiki Disk Usage, table size: ERROR: could not open relation with OID 0

[Hans Schou]

On Thu, Mar 15, 2018 at 12:32 AM, Michael Paquier 
wrote:


> > I'm running version 9.1.9 so it should be working according to the
> > wiki.
>
> You should update and upgrade.  9.1 has fallen out of community support
>

I will recommend that to the database owner. Thanks

Re: wiki Disk Usage, table size: ERROR: could not open relation with OID 0

[Hans Schou]

On Thu, Mar 15, 2018 at 12:44 AM, Tom Lane  wrote:

>
> The query does fail on < 9.2, because on rows with no reltoastrelid
>

Thats, fine. I will live with that until upgrade.


> But hey, it's a wiki;
> if you feel more ambitious, edit away.
>

I tried but it said:
"The site you are trying to log in to (the postgresql wiki) requires a
cool-off period between account creation and logging in. Please try again
later, or contact the postgresql.org webmasters if you have an urgent need
to log in."

Grant to a group defined in Windows AD

[Hans Schou]

Hi

Can I have a advise on how to handle groups?

In my Windows AD (Active Directory) I have two groups named:
  readers
  writers

In Postgresql I have these databases:
  d1
  d2

The "writers" should have NOCREATEDB etc but ALL PRIVILEGES to d1 and d2.

The "readers" should have SELECT to all tables in d1 and no access at all
to d2.

It seems like I can either use a group ROLE or a SCHEME to accomplish my
goal. Which one will be the most best/simple to administer for me and my
colleagues? Or is there another approach we can use?

Here is my draft for a daily-run Powershell script which will get all the
users in the group "readers" in the Windows AD and create them in
Postgresql:

$LdapCon = "LDAP://CN=readers,OU=specimen,DC=example,DC=org"
Write-Host "-- Get group members from: $($LdapCon)"
$Group = [ADSI]$LdapCon
$Group.Member | ForEach-Object {
$Searcher = [adsisearcher]"(distinguishedname=$_)"
$u = $($searcher.FindOne().Properties.samaccountname).ToLower()
Write-Host "CREATE ROLE `"$u`";"
Write-Host " ALTER ROLE `"$u`" WITH LOGIN;"
Write-Host " GRANT SELECT ... readers ...;"
}

And then I pipe the output to psql.exe.
The output looks like:
  CREATE ROLE "joe";
   ALTER ROLE "joe" WITH LOGIN;
   GRANT SELECT ... readers ...;

PS: To get a list of your own groups use Powershell:

([ADSISEARCHER]"samaccountname=$($env:USERNAME)").Findone().Properties.memberof

Old active connections?

[Hans Sebastian]

Hello group,

We run postgresql 10.3 for a python django app with gunicorn on nginx with
django version 1.9.5.

Recently, we started noticing there are many active connections from the
django app server that are more than 1 week old still showing in
pg_stat_activity.

Even though the django server has been stopped (all processes killed), the
active connections still persist. All of these connections are UPDATE
queries that look pretty normal.

Does anyone know the reasons they could be there? What could have caused
them being still active?

This has become an issue as we started getting "FATAL:  remaining
connection slots are reserved for non-replication superuser connections"

Thanks,
-hans

Re: Load data from a csv file without using COPY

[Hans Schou]

On Tue, Jun 19, 2018 at 10:17 PM Ravi Krishna  wrote:

> In order to test a real life scenario (and use it for benchmarking) I want
> to load large number of data from csv files.
> The requirement is that the load should happen like an application writing
> to the database ( that is, no COPY command).


Once you have parsed the data it is fairly easy to use PostgreSQL "COPY
FROM stdin" format. If you have all data with a tabulator separator. A
simple table (t1) could look like:

COPY t1 (f1,f2) FROM stdin;
3Joe
7Jane
\.

These data can be piped directly to psql and it will be fast.

Note: NULL should be '\N', see manual:
https://www.postgresql.org/docs/current/static/sql-copy.html

It is the same kind of data you get with pg_dump.

./hans

Shared buffers increased but cache hit ratio is still 85%

[Hans Schou]

Hi

I have this system with some databases and I have run the
cache_hit_ratio.sql script on it. It showed that the db acme777booking had
a ratio on 85%. I then changed shared_buffer size from 0.5GB to 4GB as the
server has 16GB of physical RAM. After 6 days of running I checked the
ratio again and it is still 85%.
Am I doing something wrong or should some history be cleared?


cache_hit_ratio.sql
datname | blks_read  |   blks_hit   | cachehitratio
++--+---
 acme777web |   50225009 |   3157586919 | 98.43
 acmelog| 462198 | 14332508 | 96.88
 acme777domain  | 7540616252 | 119574349075 | 94.07
 acme777booking |  337915568 |   1902310783 | 84.92
(4 rows)

pg_runtime.sql
   pg_start|runtime
---+
 2018-07-12 06:49:48.654252+02 | 6 days 02:44:09.488978
(1 row)

get_version_num.sql
 Version text |  Num
--+---
 9.1.9| 90109
(1 row)

SELECT pg_stat_database.datname,
   pg_stat_database.blks_read,
   pg_stat_database.blks_hit,
   round((pg_stat_database.blks_hit::double precision
  / (pg_stat_database.blks_read
 + pg_stat_database.blks_hit
 +1)::double precision * 100::double precision)::numeric,
2) AS cachehitratio
   FROM pg_stat_database
  WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
  ORDER BY round((pg_stat_database.blks_hit::double precision
 / (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double
precision)::numeric, 2) DESC;

OS: Red Hat Enterprise Linux Server release 6.4 (Santiago)

Re: Shared buffers increased but cache hit ratio is still 85%

[Hans Schou]

On Wed, Jul 18, 2018 at 10:44 AM Andreas Kretschmer 
wrote:

>
> ||pg_stat_reset()
>

Thanks, I guess we can see the result in a few days.

BTW, strang command: it only reset current database and it can't take db as
parameter.

Re: Swap on postgres master server

[Hans Schou]

Are you sure that swap is used actively? Maybe it had just been used during
backup or something.

Look after SwapIn/SwapOut (si/so) it should be '0'
$ vmstat 1
procs ---memory-- ---swap-- -io -system--
--cpu-
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id
wa st
 1  0 12  89344  46608 5863840012 8   30   86  0  0 99
0  0

If you want to see the amount of ram used by each program with childs run
this:
ps -A --sort -rss -o comm,pmem | awk '
  NR == 1 { print; next }
  { a[$1] += $2 }
  END {
for (i in a) {
  printf "%-15s\t%s\n", i, a[i];
}
  }
'


On Tue, Oct 16, 2018 at 11:04 AM Nicola Contu 
wrote:

> Hello,
> we are running Postgres 10.5 with master slave replication.
>
> These are our custom params
>
> archive_command = 'pgbackrest --stanza=cmdprod archive-push %p' # command
> to use to archive a logfile segment
> archive_mode = on # enables archiving; off, on, or always
> checkpoint_completion_target = 0.7 # checkpoint target duration, 0.0 - 1.0
> default_text_search_config = 'pg_catalog.english' #
> datestyle = 'iso, mdy' #
> effective_cache_size = 120GB #
> hot_standby = on# "on" allows queries during
> recovery
> lc_messages = 'en_US.UTF-8'  # locale for system
> error message
> lc_monetary = 'en_US.UTF-8'  # locale for monetary
> formatting
> lc_numeric = 'en_US.UTF-8'  # locale for number
> formatting
> lc_time = 'en_US.UTF-8'  # locale for time
> formatting
> listen_addresses = '*' # defaults to 'localhost', '*' = all
> log_autovacuum_min_duration = 1000ms# -1 disables, 0 logs all
> actions and
> log_checkpoints = on #
> log_line_prefix = '%t [%r] [%p]: [%l-1] db=%d,user=%u ' #
> log_lock_waits = on  # log lock waits >=
> deadlock_timeout
> log_min_duration_statement = 1000ms# -1 is disabled, 0 logs all
> statements
> log_statement = 'ddl'  # none, ddl, mod, all
> log_temp_files = 1024kB  # log temporary files equal
> or larger
> maintenance_work_mem = 2GB #
> max_connections = 220 #
> max_parallel_workers_per_gather = 8# taken from
> max_worker_processes
> max_wal_size = 2GB #
> min_wal_size = 1GB #
> pg_stat_statements.max = 1 #
> pg_stat_statements.track = all #
> port = 5432# port number which Postgres listen
> shared_buffers = 10GB #
> shared_preload_libraries = 'pg_stat_statements'  # (change
> requires restart)
> synchronous_standby_names = '1 ( "usnyh2" )' # comment out during upgrade
> track_activity_query_size = 16384# (change requires restart)
> track_io_timing = on #
> wal_buffers = 16MB #
> wal_keep_segments = 100 #
> wal_level = replica#  minimal, replica, or logical
> work_mem = 600MB #
>
> This server is on Centos 7 and the strange thing is that we see a lot of
> swap usage :
>
> [root@usnyh-cmd1 ~]# free -m
>   totalusedfree  shared  buff/cache
>  available
> Mem: 25765275555559   12804  244536
> 236036
> Swap: 1638373269057
>
> 7GB used.
>
> But can't see it from any of the commands like top etc.
> I am sure it is postgres because it is the only service running on that
> machine.
>
> Is there anything we can do?
> On the sync slave, the usage is just 400MB.
>
> Any trick?
>
> Thanks a lot,
> Nicola
>

GIST/GIN index not used with Row Level Security

[Derek Hans]

When using row level security, GIN and GIST indexes appear to get ignored.
Is this expected behavior? Can I change the query to get PostgreSQL using
the index? For example, with RLS enabled, this query:

select * from search where search like '%yo'

Creates this query plan:
"Seq Scan on search  (cost=0.00..245.46 rows=1 width=163)"
"  Filter: (((tenant_name)::name = CURRENT_USER) AND (search ~~
'%yo'::text))"

Running this same query with the owner of the table, thereby disabling RLS,
the index gets used as expected:
"Bitmap Heap Scan on search  (cost=4.49..96.33 rows=44 width=163)"
"  Recheck Cond: (search ~~ '%yo'::text)"
"  ->  Bitmap Index Scan on search__gist  (cost=0.00..4.48 rows=44 width=0)"
"Index Cond: (search ~~ '%yo'::text)"

I see the same behavior with more complex queries, switching to GIN index,
more complex RLS rules, using word_similarity instead of like, using full
text search and larger data sets (e.g. 100k rows). This is on PostgreSQL
v11.1 on Windows 10.

-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: GIST/GIN index not used with Row Level Security

[Derek Hans]

>
>
> What are the RLS policies on the table?
>
> From select * from pg_policies:
"((tenant_name)::name = CURRENT_USER)"


> What is the definition of the GIN index?
>
> CREATE INDEX search__gist
ON public.search USING gist
(search COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE pg_default;


> Best guess is the RLS is preventing access to the field needed by the
> index.
>
> I didn't realize RLS can limit access to a specific field/index - my
understanding was that it only affects what rows get returned/can be
update/inserted.


>
> >
> > select * from search where search like '%yo'
> >
> > Creates this query plan:
> > "Seq Scan on search  (cost=0.00..245.46 rows=1 width=163)"
> > "  Filter: (((tenant_name)::name = CURRENT_USER) AND (search ~~
> > '%yo'::text))"
> >
> > Running this same query with the owner of the table, thereby disabling
> > RLS, the index gets used as expected:
> > "Bitmap Heap Scan on search  (cost=4.49..96.33 rows=44 width=163)"
> > "  Recheck Cond: (search ~~ '%yo'::text)"
> > "  ->  Bitmap Index Scan on search__gist  (cost=0.00..4.48 rows=44
> width=0)"
> > "Index Cond: (search ~~ '%yo'::text)"
> >
> > I see the same behavior with more complex queries, switching to GIN
> > index, more complex RLS rules, using word_similarity instead of like,
> > using full text search and larger data sets (e.g. 100k rows). This is on
> > PostgreSQL v11.1 on Windows 10.
> >
> > --
> > *Derek*
> > +1 (415) 754-0519 |derek.h...@gmail.com  |
> > Skype: derek.hans
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: GIST/GIN index not used with Row Level Security

[Derek Hans]

>
>
> Your example is obscuring the issue by incorporating a tenant_name
> condition (where did that come from, anyway?) in one case and not
> the other.  Without knowing how selective that is, it's hard to
> compare the EXPLAIN results.
>
>
That's RLS kicking in - RLS condition is defined as
((tenant_name)::name = CURRENT_USER)


> However, wild-guess time: it might be that without access to the
> table statistics, the "search like '%yo'" condition is estimated
> to be too unselective to make an indexscan profitable.  And putting
> RLS in the way would disable that access if the ~~ operator is not
> marked leakproof, which it isn't.
>

I didn't realize you could set access to table statistics. How do I enable
this access for this user? If that's not possible, it sounds like it
effectively blocks the use of GIN/GIST indexes when RLS is in use.


> I'm not sure that you should get too excited about this, however.
> You're evidently testing on a toy-size table, else the seqscan
> cost estimate would be a lot higher.  With a table large enough
> to make it really important to guess right, even the default
> selectivity estimate might be enough to get an indexscan.
>
>
I've tried this with larger data sets, with the same results. I discovered
this problem because the select was taking 10-30 seconds instead of the
expected sub-second, when using larger data sets and more fields getting
searched. The example is the simplest repro case I could create.



> regards, tom lane
>


-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: GIST/GIN index not used with Row Level Security

[Derek Hans]

Thanks for the pointer for marking functions as leakproof, I was unaware of
that whole concept.

Unfortunately only "alter function" supports "leakproof" - "alter operator"
does not. Is there a function-equivalent for marking operators as
leakproof? Is there any documentation for which operators/functions are
leakproof?

In my particular case, RLS is still useful even if operators are leaky as I
control the application code and therefore can ensure leaky errors are
handled. If it's possible to disable all checking for "leakproof", that
would work for me.

> If that's not possible, it sounds like it
> > effectively blocks the use of GIN/GIST indexes when RLS is in use.
>
> There's a whole lot of daylight between "it doesn't pick an indexscan in
> this one example" and "it effectively blocks the use of GIN/GIST".
>

True indeed :). Would you have a working example of using a GIN/GIST index
with RLS? All the attempts I've made have ended in seq scans. In practice,
I'm looking to implement fuzzy search using trigrams, so % and %> operators
are what matter to me. ~~ also happens to fail. Should I expect to be able
to use any of these with RLS, large amounts of data and reasonable
performance?

Your description of leakproof (and the documentation I've found) makes it
sound like I'm not just hitting an isolated problem, but a general problem
with RLS that represents a substantial limitation and is likely worth
documenting.

-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: GIST/GIN index not used with Row Level Security

[Derek Hans]

Thanks for the detailed response, super helpful in understanding what's
happening, in particular understanding the risk of not marking functions as
leakproof. I'll take a look at the underlying code to understand what's
involved in getting a function to be leakproof.

That said, it does seem like it should be possible and reasonable to
specify that a user should have access to the table stats so that the query
planner works as expected. Maybe it comes down to the fact that RLS is
still a work in progress, and I shouldn't be relying on it unless I'm
really certain it supports the functionality I need.

I've updated word_similarity_op(text,text) to be leakproof, and
pg_proc agrees it is. I'm assuming word_similarity_op() is equivalent to
<%, though I haven't found explicit confirmation. However, using
word_similarity() instead of <% on a 100k row table, without any RLS
involved, doesn't make use of the index, while using <% does. Obviously,
adding the RLS doesn't make that any better. Any idea what might be the
cause?


On Tue, Aug 13, 2019 at 5:39 PM Stephen Frost  wrote:

> Greetings,
>
> * Derek Hans (derek.h...@gmail.com) wrote:
> > Unfortunately only "alter function" supports "leakproof" - "alter
> operator"
> > does not. Is there a function-equivalent for marking operators as
> > leakproof? Is there any documentation for which operators/functions are
> > leakproof?
>
> Tom's query downthread provides the complete list.
>
> Note that the list is not completely static- it's entirely possible that
> additional functions can be made leak-proof, what's needed is a careful
> review of the function code to ensure that it can't leak information
> about the data (or, if it does today, a patch which removes that).  If
> you have an interest in that then I'd encourage you to dig into the code
> and look for possible leaks (Tom's already hinted in the direction you'd
> want to go in) and then propose a patch to address those cases and to
> mark the function(s) as leakproof.
>
> > In my particular case, RLS is still useful even if operators are leaky
> as I
> > control the application code and therefore can ensure leaky errors are
> > handled. If it's possible to disable all checking for "leakproof", that
> > would work for me.
>
> There isn't a way to disable the leakproof-checking system.  Certainly
> in the general case that wouldn't be acceptable and I'm not entirely
> convinced by your argument that such an option should exist, though you
> could go through and set all of the functions to be leakproof if you
> really wish to.
>
> > > If that's not possible, it sounds like it
> > > > effectively blocks the use of GIN/GIST indexes when RLS is in use.
> > >
> > > There's a whole lot of daylight between "it doesn't pick an indexscan
> in
> > > this one example" and "it effectively blocks the use of GIN/GIST".
> >
> > True indeed :). Would you have a working example of using a GIN/GIST
> index
> > with RLS? All the attempts I've made have ended in seq scans. In
> practice,
> > I'm looking to implement fuzzy search using trigrams, so % and %>
> operators
> > are what matter to me. ~~ also happens to fail. Should I expect to be
> able
> > to use any of these with RLS, large amounts of data and reasonable
> > performance?
>
> Functions that aren't marked leakproof aren't going to be able to be
> pushed down.
>
> > Your description of leakproof (and the documentation I've found) makes it
> > sound like I'm not just hitting an isolated problem, but a general
> problem
> > with RLS that represents a substantial limitation and is likely worth
> > documenting.
>
> There's some documentation regarding leakproof functions here:
>
> https://www.postgresql.org/docs/current/ddl-rowsecurity.html
>
> and here:
>
> https://www.postgresql.org/docs/11/sql-createfunction.html
>
> Of course, patches are welcome to improve on our documentation.
>
> One thing that it sounds like you're not quite appreciating is that in
> the general case, verifying that a function is leakproof isn't optional.
> Without such a check, any user could create a function and then get PG
> to push that function down below the RLS checks and therefore gain
> access to the data that they aren't supposed to be able to see.
>
> All that said, there's quite a few functions that *are* marked as
> leakproof already and they're quite handy and work well with RLS
> already, as I expect you'll see when you go querying pg_proc.
>
> Thanks,
>
> Stephen
>


-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: GIST/GIN index not used with Row Level Security

[Derek Hans]

>
>
> > I've updated word_similarity_op(text,text) to be leakproof, and
> > pg_proc agrees it is. I'm assuming word_similarity_op() is equivalent to
> > <%, though I haven't found explicit confirmation. However, using
> > word_similarity() instead of <% on a 100k row table, without any RLS
> > involved, doesn't make use of the index, while using <% does. Obviously,
> > adding the RLS doesn't make that any better. Any idea what might be the
> > cause?
>
> Just to be clear, you should be looking at pg_operator (oprcode) to
> determine the function that is under the operator that you wish to
> change to being leakproof.
>
>
Thanks for that pointer.


> Note that the selectivity functions are associated with the operator,
> not the function itself.
>

That was the missing piece, thanks. How come operators get optimized but
functions don't?

Quick summary:
The text similarity/full text search/like operators are not marked as
leakproof, which stops them from having access to table statistics. When
combined with row level security, operators that aren't leakproof can't get
pushed down and therefore happen after the RLS check, preventing use of
GIN/GIST indexes. A workaround is marking the underlying function as
leakproof but that is only reasonable because our particular setup makes it
acceptable if information leaks via database error messages.

To resolve:
- Lookup function associated with operator being used via the pg_operator
table
- Check if that function is leakproof based on info in pg_proc table
- ALTER FUNCTION func LEAKPROOF
- Use original operator in code - the underlying function doesn't get
optimized and bypasses the index

While those steps work on my local machine, unfortunately we're deployed on
AWS Aurora which doesn't allow marking functions as leakproof. Functions
are owned by the rdsadmin user and controlled by AWS. In practice, that
appears to mean that fuzzy search/full text search with reasonable
performance isn't compatible with RLS on Amazon Aurora. We may end up
setting up Elasticsearch to support text search. In any case, we need to
separate search from checking who is allowed to see the results.

Thanks for the help from everyone!

Re: backing up the data from a single table?

[Hans Schou]

On Fri, Sep 13, 2019 at 4:14 PM stan  wrote:

> Is there a way to "export" a single table, that can be easily re
> "imported"?
>

Export:

pg_dump --table=foo > foo.sql

Import:

cat foo.sql | psql

Re: What is the tuplestore?

[Hans Schou]

When one get a "No space left on device" and there is a lot of space it is
sometimes caused by lack of inodes.

Try run the command:
  df --inodes


On Mon, Dec 10, 2018 at 4:56 PM Ron  wrote:

> Hi,
>
> v9.6.6
>
>
> 2018-12-07 06:21:55.504 EST 10.140.181.89(35868) CDS CDSLBXW 13748 SELECT
> PostgreSQL JDBC Driver 53100 ERROR:  could not write to tuplestore
> temporary
> file: No space left on device
>
> I see this in the pg_log file, but #1 can't figure out what "tuplestore"
> is
> (Google doesn't help), and #2 there's lots of space on all my file
> systems.
> data/base, where pgsql_tmp lives, has 96GB free.)
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>

Re: WSL (windows subsystem on linux) users will need to turn fsync off as of 11.2

[Hans Schou]

On Fri, Feb 15, 2019 at 1:34 AM Bruce Klein  wrote:

>
> If you are running Postgres inside Microsoft WSL
>

https://docs.microsoft.com/en-us/windows/wsl/faq
Who is WSL for?
This is primarily a tool for developers ...
---

One problem with WSL is that the I/O performance is not good and it might
never be solved. So using WSL for production is not what it was ment for.

WSL is called a "compatibility layer". When running WSL there is no Linux
kernel despite "uname" say so. Like WINE, where one can run Windows
binaries on Linux but there is no Windows OS.
https://en.wikipedia.org/wiki/Compatibility_layer

That said, WSL is a great tool for developers. Better than Cygwin.

./hans

Update does not move row across foreign partitions in v11

[Derek Hans]

I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
with 2 local partitions and 2 partitions on instance B using foreign data
wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
Inserting rows into this table works as expected, with rows ending up in
the appropriate partition. However, updating those rows only moves them
across partitions in some of the situations:

   - From local partition to local partition
   - From local partition to foreign partition

Rows are not moved

   - From foreign partition to local partition
   - From foreign partition to foreign partition

Is this the expected behavior? Am I missing something or configured
something incorrectly?

Thanks,
Derek

Re: Update does not move row across foreign partitions in v11

[Derek Hans]

Hi all,
This behavior makes the new data sharding functionality in v11 only
marginally useful as you can't shard across database instances.
Considering data sharding appeared to be one of the key improvements in
v11, I'm confused - am I misunderstanding the expected functionality?

Thanks!

On Fri, Feb 22, 2019 at 9:44 AM Derek Hans  wrote:

> I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
> with 2 local partitions and 2 partitions on instance B using foreign data
> wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
> Inserting rows into this table works as expected, with rows ending up in
> the appropriate partition. However, updating those rows only moves them
> across partitions in some of the situations:
>
>- From local partition to local partition
>- From local partition to foreign partition
>
> Rows are not moved
>
>- From foreign partition to local partition
>- From foreign partition to foreign partition
>
> Is this the expected behavior? Am I missing something or configured
> something incorrectly?
>
> Thanks,
> Derek
>


-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: Update does not move row across foreign partitions in v11

[Derek Hans]

Based on a reply to reporting this as a bug, moving rows out of foreign
partitions is not yet implemented so this is behaving as expected. There's
a mention of this limitation in the Notes section of the Update docs.

On Wed, Feb 27, 2019 at 6:12 PM Alvaro Herrera 
wrote:

> On 2019-Feb-22, Derek Hans wrote:
>
> > I've set up 2 instances of PostgreSQL 11. On instance A, I created a
> table
> > with 2 local partitions and 2 partitions on instance B using foreign data
> > wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
> > Inserting rows into this table works as expected, with rows ending up in
> > the appropriate partition. However, updating those rows only moves them
> > across partitions in some of the situations:
> >
> >- From local partition to local partition
> >- From local partition to foreign partition
> >
> > Rows are not moved
> >
> >- From foreign partition to local partition
> >- From foreign partition to foreign partition
> >
> > Is this the expected behavior? Am I missing something or configured
> > something incorrectly?
>
> Sounds like a bug to me.
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


-- 
*Derek*
+1 (415) 754-0519 | derek.h...@gmail.com | Skype: derek.hans

Re: Installing PostgreSQL on Oracle Solaris

[Hans Schou]

On Sat, Mar 23, 2019 at 3:48 PM Igor Korot  wrote:

>
> You mean even running as "sudo"?
>

igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2
| tar xpf -

No, you are not running tar as sudo.

I would at least suggest:
  cd /usr/local/src
  sudo tar --bzip2 xf /usr/postgresql-9.6.1-S11.i386-64.tar.bz2

a bit dependen of which tar you have.

otherwise:
  cd /usr/local/src
  sudo tar xf <( bzcat /usr/postgresql-9.6.1-S11.i386-64.tar.bz2 )

Re: software or hardware RAID?

[Hans Schou]

On Sun, Mar 17, 2019 at 11:54 PM Rory Campbell-Lange <
r...@campbell-lange.net> wrote:

> We aren't sure whether to use software MDRaid or a MegaRAID card.
>

Never go with hardRaid.  I have had a breakdown on a hardware RAID and as
it was special and not off-the-shelf, I could not move the disk to another
controller. I think it was a capacitor, maybe capasitor plaegue.
Only thing I had to do was to restore to the day before and the customer
lost one days work.

>From that on, I only use softRAID.

Re: Memory settings

[Hans Schou]

Try run postgresqltuner.pl as suggested on
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and also
look at the other info there.

After running a few days with live data run cache_hit_ratio.sql by Melvin
Davidson:
SELECT pg_stat_database.datname,
   pg_stat_database.blks_read,
   pg_stat_database.blks_hit,
   round((pg_stat_database.blks_hit::double precision
  / (pg_stat_database.blks_read
 + pg_stat_database.blks_hit
 +1)::double precision * 100::double precision)::numeric,
2) AS cachehitratio
   FROM pg_stat_database
  WHERE pg_stat_database.datname !~ '^(template(0|1)|postgres)$'::text
  ORDER BY round((pg_stat_database.blks_hit::double precision
 / (pg_stat_database.blks_read
+ pg_stat_database.blks_hit
+ 1)::double precision * 100::double
precision)::numeric, 2) DESC;

The real question is: Is your system slow?


On Sun, Jun 30, 2019 at 5:14 AM Daulat Ram 
wrote:

> Hi team,
>
>
>
> Can you please suggest what will be  the suitable memory settings for
> Postgresql11 if we have 80gb RAM, 16 CPU’s and OS  Linux.
>
>
>
> If we set 25 % of total RAM then shared_buffers value will be 20GB. Will
> it be useful or we can set it any random vale like 8g or 12gb.
>
>
>
> According to https://pgtune.leopard.in.ua/#/
>
> below are the suggested memory  values for 80gb RAM and 16 CPU.  I assume
> the values preferred for effective_cache_size = 60GB and shared_buffers =
> 20GB are too large.
>
>
>
> max_connections = 500
>
> shared_buffers = 20GB
>
> effective_cache_size = 60GB
>
> maintenance_work_mem = 2GB
>
> checkpoint_completion_target = 0.7
>
> wal_buffers = 16MB
>
> default_statistics_target = 100
>
> random_page_cost = 1.1
>
> effective_io_concurrency = 300
>
> work_mem = 6553kB
>
> min_wal_size = 1GB
>
> max_wal_size = 2GB
>
> max_worker_processes = 16
>
> max_parallel_workers_per_gather = 8
>
> max_parallel_workers = 16
>
>
>
> Please give your suggestions.
>
>
>
> Regards,
>
> Daulat
>
>
>

Feature request: pg_get_tabledef(text)

[Hans Schou]

Hi

Similar to pg_get_viewdef() and pg_get_functiondef() it would be useful
with a pg_get_tabledef() to get a full description of how a table is
defined.

Currently the table definition can be extracted with the command:

  pg_dump -d foo --schema-only --table=bar | egrep '^[^-]'

The psql command '\d bar' gives some of the same information but it is not
in a format where it can be used to create a table.

Extra:
With the pg_get_tabledef() function in place it is very close to be
possible to implement pg_dump() within the system. So instead of running:
  pg_dump -d foo
one could just run:
  psql -d foo -c 'SELECT pg_dump()'

The function could also be called from within a programming language like
Java/PHP.

pg_dump has a lot of options where some of them could be parameters to the
pg_dump() function. If using a cloud or other webservice this will be an
easy way to make an extra backup.

-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: Trainning and Certification

[Hans Schou]

On Wed, Dec 6, 2023 at 3:39 PM roger popa  wrote:

> You can tell if exists an oficial Postgresql Certification issued by
> postgresql.org ?
>

No.


> Or others (like PearsonVue)?
>

EnterpriseDB has certifications. I think you can get some of them for free.
https://www.enterprisedb.com/accounts/register/biganimal


-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: Feature request: pg_get_tabledef(text)

[Hans Schou]

On Wed, Nov 22, 2023 at 5:09 PM Laurenz Albe 
wrote:

>
> One of the problems is what should be included.
> Indexes?  Policies?  Constraints?
>

A high limit could be all objects except data.
All the objects which would be deleted by a 'DROP TABLE'.

Maybe including 'CASCADE'?

No unsurmountable questions, but someone would have to come up with a
> clear design and implement it.
>

I gave it a try.
I'm not that skilled in plpgsql so there is probably room for improvement.

https://github.com/chlordk/pg_get_tabledef

For your convenience here is a copy/paste of the function.

CREATE OR REPLACE FUNCTION pg_get_tabledef(TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $_$
-- pg_get_tabledef ( text ) → text
-- Reconstructs the underlying CREATE command for a table and objects
related to a table.
-- (This is a decompiled reconstruction, not the original text of the
command.)
DECLARE
R TEXT; -- Return result
R_c TEXT; -- Comments result, show after table definition
rec RECORD;
tmp_text TEXT;
v_oid OID; -- Table object id
v_schema TEXT; -- Schema
v_table TEXT; -- Table name
rxrelname TEXT;
BEGIN
rxrelname :=  '^(' || $1 || ')$';
-- Get oid and schema
SELECT
c.oid, n.nspname, c.relname
INTO
v_oid, v_schema, v_table
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) rxrelname COLLATE
pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid);
-- If table not found exit
IF NOT FOUND THEN
-- RAISE EXCEPTION 'Table % not found', $1;
RETURN '-- Table not found: ''' || $1 || ;
END IF;
-- Table comment first, columns comment second, init variable R_c,
SELECT obj_description(v_oid) INTO tmp_text;
IF LENGTH(tmp_text) > 0 THEN
R_c := 'COMMENT ON TABLE ' || v_schema || '."' || v_table || '" IS
''' || tmp_text || ''';' || E'\n';
ELSE
R_c := '';
END IF;
R := 'CREATE TABLE ' || v_schema || '."' || v_table || '" (';
-- Get columns
FOR rec IN
SELECT
a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
 FROM pg_catalog.pg_attrdef d
 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c,
pg_catalog.pg_type t
 WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attgenerated,
a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = v_oid AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
--RAISE NOTICE '% % %', rec.attnum, rec.attname, rec.format_type;
IF rec.attnum > 1 THEN
R := R || ','; -- no comma after last column definition
END IF;
R := R || E'\n' || '"' || rec.attname || '" ' ||
rec.format_type;
IF rec.attnotnull THEN
R := R || ' NOT NULL';
END IF;
-- Comment on column
SELECT col_description( v_oid, rec.attnum) INTO tmp_text;
IF LENGTH(tmp_text) > 0 THEN
R_c := R_c || 'COMMENT ON COLUMN ' || v_schema || '."' ||
v_table || '.' || rec.attname || '" IS ''' || tmp_text || ''';' || E'\n';
END IF;
END LOOP; -- Columns
-- Finalize table
R := R || E'\n' || ');' || E'\n';
-- Add COMMENTs
IF LENGTH(R_c) > 0 THEN
R := R || R_c;
END IF;
-- Index
FOR rec IN
SELECT
pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid
AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = v_oid AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname
LOOP
R := R || rec.indexdef || ';' || E'\n';
END LOOP; -- Index
RETURN R;
END;
$_$;



-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: Password forgotten

[Hans Schou]

Have you tried

   sudo -u postgres psql


On Tue, Apr 23, 2024 at 2:14 PM Arbol One  wrote:

> Hello.
> In my Debian box, after entering this command to psql-16, *psql -h
> localhost -U postgres*
>
> psql-16 asks for the password, which I have forgotten.
> So I get this message:
>
>
>
> *psql: error: connection to server at "localhost" (::1), port 5432 failed:
> FATAL:  password authentication failed for user "postgres" connection to
> server at "localhost" (::1), port 5432 failed: FATAL:  password
> authentication failed for user "postgres" *
>
> Is there a way I can retrieve this master password?
>
> Thanks in advance.
>
>
> --
> *ArbolOne.ca* Using Fire Fox and Thunderbird. ArbolOne is composed of
> students and volunteers dedicated to providing free services to charitable
> organizations. ArbolOne on Java Development is in progress [ í ]
>


-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: utf8 vs UTF-8

[Hans Schou]

> test3   | troels   | UTF8 | libc| en_US.utf8 |
en_US.utf8

It is wrong but I guess it's working?

how did you create test3?

On Fri, May 17, 2024 at 2:44 PM Troels Arvin  wrote:

> Hello,
>
> In a Postgres installation, I have databases where the locale is
> slightly different. Which one is correct? Excerpt from "psql --list":
>
>   test1   | loc_test | UTF8 | libc| en_US.UTF-8 |
> en_US.UTF-8
>   test3   | troels   | UTF8 | libc| en_US.utf8 |
> en_US.utf8
>
> OS is Ubuntu.
>
> --
> Kind regards,
> Troels Arvin
>
>
>
>

-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Oracle Linux 9 Detected RPMs with RSA/SHA1 signature

[Hans Schou]

Hi

On my test server I have Oracle Linux 8.10 installed.
Here I have installed postgresql 16.1 from postgresql.org repository.

Upgrade to Oracle Linux 9:
When doing a »leapp preupgrade --oraclelinux« I get the message below.

I want to have postgresql.org as my repo for PostgreSQL and Oracle Linux
for the rest. But it fails due to this SHA1 signature.

As Oracle Linux 8 since April 2024 now have PostgreSQL 16.1 in the repo I
could just disable the pg-repo and use the ol-repo. But is this the
recommended way to do it?


Output from /var/log/leapp/leapp-report.txt

Risk Factor: high (inhibitor)
Title: Detected RPMs with RSA/SHA1 signature
Summary: Digital signatures using SHA-1 hash algorithm are no longer
considered secure and are not allowed to be used on OL 9 systems by
default. This causes issues when using DNF/RPM to handle packages with
RSA/SHA1 signatures as the signature cannot be checked with the default
cryptographic policy. Any such packages cannot be installed, removed, or
replaced unless the signature check is disabled in dnf/rpm or SHA-1 is
enabled using non-default crypto-policies. For more information see the
following documents:
  - Major changes in OL 9:
https://docs.oracle.com/en/operating-systems/oracle-linux/9/relnotes9.4/ol9-NewFeaturesandChanges.html
  - Security Considerations in adopting OL 9:
https://docs.oracle.com/en/operating-systems/oracle-linux/9/security/security-ImplementingAdditionalSecurityFeaturesandBestPractices.html#system-crypto-policies
 The list of problematic packages:
- libpq5 (DSA/SHA1, Fri 15 Sep 2023 12:11:13 PM CEST, Key ID
1f16d2e1442df0f8)
- postgresql16 (DSA/SHA1, Mon 20 Nov 2023 10:56:22 AM CET, Key ID
1f16d2e1442df0f8)
- pgdg-redhat-repo (DSA/SHA1, Thu 14 Sep 2023 02:41:37 PM CEST, Key ID
1f16d2e1442df0f8)
- postgresql16-libs (DSA/SHA1, Mon 20 Nov 2023 10:56:22 AM CET, Key ID
1f16d2e1442df0f8)
- postgresql16-contrib (DSA/SHA1, Mon 20 Nov 2023 10:56:23 AM CET, Key
ID 1f16d2e1442df0f8)
- postgresql16-server (DSA/SHA1, Mon 20 Nov 2023 10:56:22 AM CET, Key
ID 1f16d2e1442df0f8)
Related links:
- Major changes in OL 9:
https://docs.oracle.com/en/operating-systems/oracle-linux/9/relnotes9.4/ol9-NewFeaturesandChanges.html
- Security Considerations in adopting OL 9:
https://docs.oracle.com/en/operating-systems/oracle-linux/9/security/security-ImplementingAdditionalSecurityFeaturesandBestPractices.html#system-crypto-policies
Remediation: [hint] It is recommended that you contact your package vendor
and ask them for new builds signed with supported signatures and install
the new packages before the upgrade. If this is not possible you may
instead remove the incompatible packages.
Key: f16f40f49c2329a2691c0801b94d31b6b3d4f876

-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: Finding error in long input file

[Hans Schou]

If the file has these line breaks you show, then can make it to multiple
'INSERT INTO' instead.

Search for lines starting with parentese begin '(' and replace it with the
correct INSERT and last comma to semi-colon:
  cat i.sql | sed -e 's/^(/INSERT INTO foo VALUES(/' -e 's/,$/;/'

Does the file come from mysqldump? Then try option --extended-insert=FALSE

On Wed, Jul 10, 2024 at 2:53 PM Rich Shepard 
wrote:

> On Tue, 9 Jul 2024, Craig McIlwee wrote:
>
> > The input file is 488 lines (presumably, since Rich said the file should
> > insert 488 rows). It seems like too much of a coincidence that the last
> > character of the last line is really the error. My guess is that there is
> > an unmatched character, perhaps a parenthesis, that is throwing off the
> > parser because it doesn't expect the statement to terminate yet. Maybe
> > that unmatched char really is on the last line, but '85250 Red House Rd'
> > doesn't seem like the issue. I don't know anything about the joe editor,
> > but I'd hope that any decent editor with syntax highlighting would make
> it
> > apparent where things went awry.
>
> Craig, et al.,
>
> I use emacs for scripts and coding, joe's only for small jobs.
>
> I added a line to the file so the bottom line is now 489. The attached
> image
> shows that line is the only one terminated with a semicolon rather than a
> comma.
>
> psql would tell me if there was no closing parenthesis on a line, if the
> terminating comma was missing, or other similar error, and would tell me
> the
> number of the line or following line. Having the error marked at the end of
> the file does not tell _me_ just where the error actually is.
>
> Partial screenshot attached.
>
> Thanks all,
>
> Rich



-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Re: Finding error in long input file

[Hans Schou]

On Wed, Jul 10, 2024 at 2:59 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
> And what are the first few lines of the file? Use text, not screenshots.
>

Yes the line with 'INSERT'

grep -ni 'INSERT INTO' scripts/insert-addrs.sql


-- 
𝕳𝖆𝖓𝖘 𝕾𝖈𝖍𝖔𝖚
☏ ➁➁ ➅➃ ➇⓪ ➁⓪

Huge performance penalty with parallel queries in Windows x64 v. Linux x64

[Hans Buschmann]

When developing a solution for a new customer request I created a new query 
over the production data.

Despite the relatively low row counts of the involved tables (all < 100k) I 
noticed quite a long execution time of about 85 ms to 100 ms.

The explain anaylze plan showed a parallel execution plan with 2 parallels.

The data structure and index structure was not quite optimal for this kind of 
query (which does not matter in this case).

The comparison of the explain analyze plans on win-x64 and Linux x64 showed 
about 3 times longer execution on windows.

For comparison I reinstalled the production data on two test databases on 
different virtual machines on the same hardware (the very same machine with 
Hyper-V virtualization).

The steps were only (on a mostly complete idle machine):
1. create test database
2. pg_restore of the production data from same dump file
3. analyze on the database
4. run the query multiple times (about 5 times) and took the fastest explain 
analyze.

On fedora 34 64 bit, PG 13.2 unmodified self compiled the query took about 33 
ms.
On Windows Server 2019 64 bit, PG 13.2 from EDB download packages the query 
took about 85 ms.

 version
--
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1 20210428 
(Red Hat 11.1.1-1), 64-bit

  version

 PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit

The corresponding explain plans are available at explain.depesz.com

-- fedora
https://explain.depesz.com/s/Mq3P

-- windows
https://explain.depesz.com/s/VLtZ

The main difference is the time shown for the Gather Merge step (65 ms vs. 7 ms)

The explain plans and the non-standard GUCs are included in the attachments, 
the configuration for the databases seems quite comparable.

Unfortunately I cannot disclose the query and the table data.

My experience with parallel queries is not very wide, but this massive 
execution time difference of the exact same query on the exact same data on the 
exact same hardware with the same, unmodified last stable Postgres version is 
very astonishing.

BTW I generally observed slower execution under Windows, so production has 
moved now to Linux.

There seem no relevant GUC differences concerning query execution, so the 
performance penalty of 300% to 900% (one step only) is not easily explainable.

The databases remain on the system to repeat the queries on request in the 
queue of further investigation.


Thanks for looking.

Hans Buschmann


 QUERY PLAN

 Append  (cost=8284.93..8437.72 rows=2022 width=198) (actual 
time=31.506..32.637 rows=34 loops=1)
   CTE qsum
 ->  Sort  (cost=8280.38..8284.93 rows=1822 width=180) (actual 
time=31.502..32.577 rows=22 loops=1)
   Sort Key: or_followup.of_season, orders.or_clis_sub_code
   Sort Method: quicksort  Memory: 28kB
   ->  Hash Left Join  (cost=7864.72..8181.70 rows=1822 width=180) 
(actual time=31.274..32.566 rows=22 loops=1)
 Hash Cond: (or_followup.of_season = seasons.id_sea)
 ->  Hash Left Join  (cost=7862.95..8165.94 rows=1822 
width=106) (actual time=31.252..32.523 rows=22 loops=1)
   Hash Cond: (orders.or_clis_sub_code = 
clients_sub.clis_sub_code)
   ->  Finalize GroupAggregate  (cost=7818.93..8098.89 
rows=1822 width=94) (actual time=30.987..32.251 rows=22 loops=1)
 Group Key: or_followup.of_season, 
orders.or_clis_sub_code
 ->  Gather Merge  (cost=7818.93..8028.30 rows=1518 
width=94) (actual time=30.978..32.207 rows=30 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Partial GroupAggregate  
(cost=6818.91..6853.06 rows=759 width=94) (actual time=24.688..25.201 rows=10 
loops=3)
 Group Key: or_followup.of_season, 
orders.or_clis_sub_code
 ->  Sort  (cost=6818.91..6820.80 
rows=759 width=20) (actual time=24.601..24.652 rows=1334 loops=3)
   Sort Key: or_followup.of_season, 
orders.or_clis_sub_code
   Sort Method: quicksort  Memory: 
53kB
   Worker 0:  Sort Method: 
quicksort  Memory: 179kB
   Worker 1: 

AW: Huge performance penalty with parallel queries in Windows x64 v. Linux x64

[Hans Buschmann]

>No Windows here, but could it be super slow at launching workers?  How
>does a trivial parallel query compare, something like?

>SET force_parallel_mode = on;
>EXPLAIN ANALYZE SELECT 42;

indeed this query takes about 40ms in windows and 7ms on Linux (lowest values).

Due to remoting the machine the reported times vary quite a bit.

The problem seems that this (probably inherent) performance disadvantage of 
windows is not reflected in the cost model.

This causes little to middle complex queries to prioritize parallel execution 
on windows which is certainly not the best option in these cases.

The starting of processes should have an adequate cost penalty to guide the 
planner in the right direction.

Generally disabling parallel queries seems not a viable option with mixed loads.

Here are the query plans:

 QUERY PLAN Windows

 Gather  (cost=1000.00..1000.11 rows=1 width=4) (actual time=34.995..38.207 
rows=1 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 
rows=1 loops=1)
 Planning Time: 0.016 ms
 Execution Time: 39.136 ms
(7 Zeilen)

QUERY PLAN Linux
--
 Gather  (cost=1000.00..1000.11 rows=1 width=4) (actual time=6.864..7.764 
rows=1 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.003 
rows=1 loops=1)
 Planning Time: 0.026 ms
 Execution Time: 7.812 ms
(7 rows)


Hans Buschmann

AW: Huge performance penalty with parallel queries in Windows x64 v. Linux x64

[Hans Buschmann]

Thank you Thomas for pointing me to this GUC which I haven't realized before.

>From the documentation I take that a cost of 1.0 is set for a sequential page 
>fetch.

In my opinion, even for Linux the default for parallel_setup_cost is set too 
low (1000). It should reflect the sequential access of 1000 pages, which 
normally is faster from buffer cache on modern hardware.

For Windows, these costs are much higher, so I would propose to set the default 
to at least 1, perhaps 25000 to reflect the real parallel overhead.

(BTW: Is this cost multiplied by the real count of workers choosen 
(max_parallel_workers_per_gather) or only a value independent of the number of 
workers?. This would matter in windows-high-parallel scenarios)

The inadequate default gives more and more slower-then-necessary plans when 
people are moving to newer PG versions with good parallel support. For them 
it's like for me a little surprise, which most won't even notice or remedy nor 
full understand.

For bigger installations the knowledge of query tuning is more probable and 
people can react on their real situation.

Perhaps someone with more knowledge with parallel queries can make some 
profiling / performance tests to justify my proposals (e.g. what is the 
sequential page access equivalent of 40 ms on selected platforms):

New defaults proposal:
-- Linux and comparable architectures with fast process creation:
parallel_setup_cost  =  2500

-- Windows
parallel_setup_cost = 25000


Thanks

Hans Buschmann

LZ4 missing in pg14-beta1 Windows build, OLD VS/compiler used

[Hans Buschmann]

I tried to test a customer case of using bytea columns with the new lz4 
compression.

But lz4 support is not included in the standard binaries downloadable through 
the PostreSQL Website (from EDB).

For easy testing with windows this should be enabled in the upcoming releases 
of pg14: not everybody is willing or capable of self-compiling a Windows 
distribution…

I also noticed that VS2017 is still used for pg14.

After two years it should be better to distribute the Windows version compiled 
with Visual Studio 2019

Environment:

select version ();
version
---
 PostgreSQL 14beta1, compiled by Visual C++ build 1914, 64-bit
(1 Zeile)

Thanks for investigating

Hans Buschmann

Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

Hi

FYI - if it has any interest

During my preparation for describing what happens when two processes update
the same row in a table, I came across that PostgreSQL is doing right and
Oracle is doing it wrong.

The situation is a process which get a deadlock, but because it is a
script, it sends a commit anyway. This is bad behavior by humans but that's
how they are.

After both processes commit's the table should be:
 i |  n
---+---
 1 | 11
 2 | 21
in Oracle it is:
 i |  n
---+---
 1 | 11
 2 | 22

PostgreSQL: https://youtu.be/rH-inFRMcvQ
Oracle: https://youtu.be/l2IGoaWql64

PostgreSQL:
A
select * from t;
begin;
update t set n=n+1 where i=2;

B
begin;
update t set n=n+1 where i=1;
update t set n=n+1 where i=2;

A
update t set n=n+1 where i=1;

B
commit;

A
commit;

best regards
hans

Re: Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

2017-12-21 17:46 GMT+01:00 Jeremy Finzel :

> It's hard to follow how the 2 videos relate, because you don't run the
> same SQL both places.  You first update where i = 2 in Postgres and i = 1
> in Oracle.
>

Well OK. I made a new one for PostgreSQL: https://youtu.be/En8EFv90yCc
Now with same background color.

Re: Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

2017-12-21 12:41 GMT+01:00 Rakesh Kumar :

> Could it be that the tool you are using in Oracle is doing commit while
> exiting out due to Deadlock, because there is no explicit rollback.
>

The tool Im using is "sqlplus". By default you are always in a transaction
and auto-commit only occur on exit.
Please note that Oracle leave the table with a half transaction, i.e. only
one row is updated.

Re: Deadlock with one table - PostgreSQL is doing it right

[Hans Schou]

2017-12-21 21:50 GMT+01:00 Rakesh Kumar :

> whenever SQLERROR EXIT ROLLBACK
>

Thanks. You are absolutely right.
After starting with:
   WHENEVER SQLERROR EXIT ROLLBACK
the process getting the deadlock will exit to command prompt (with
%ERRORLEVEL% = 0).

So what actually found out was that Oracle has some strange combinations of
default values regarding
1. AUTOCOMMIT = 0
2. Don't exit/rollback on deadlock

Re: psql and regex not like

[Hans Schou]

On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson  wrote:

> psql -Xc "select datname from pg_database WHERE datname \!~
> 'template|postgres' ORDER BY datname;"
>

Remove the space:

psql -Xc "select datname from pg_database WHERE datname!~
'template|postgres' ORDER BY datname"

I'm not really sure why as this one works:
psql -c "SELECT ' !'"