Building lots of indices in parallel

[Ron]

We're migrating a big database (200ish tables 30 of which have bytea or xml 
fields; the Oracle size is 10TB) to Postgresql 12.5 (RDS, if it matters), 
and after the loads are completed, it will be time to create the secondary 
indices (tables currently only have PK constraints), and they of course need 
to be built as fast as possible.


The first things to do are based in Postgres itself:
ALTER TABLE foo SET (parallel_workers = 4);
SET max_parallel_maintenance_workers TO 4;
CREATE INDEX foo_ind2 ON foo (f1);

That creates one index very quickly, but only one index at a time, wasting 
the other CPUs (there are 48 in the VM, and 384GB RAM).


What is the impact on locking/contention of using something like GNU 
Parallel (especially when it tries to create multiple indices on the same 
table in parallel)?


Is there a better way to quickly create lots of indices?

--
Angular momentum makes the world go 'round.

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

[Martin Ritchie]

I have had good luck with security barrier views and performance. The main
thing security barrier does is ensure that where statements are processed
correctly.

The big consideration IMHO is how many tenants are you dealing with. A
couple of tenants, then best to give them separate databases. More than a
couple, but less than 50 or so, then best to give them all separate
schemas. If there are thousands of tenants (like an internet application)
then security barrier views are usually the easiest option to manage.

Martin Ritchie
*Geotab*
Senior DBA
Direct +1 (519) 741-7660
Toll-free +1 (877) 436-8221
Visit www.geotab.com
Twitter  | Facebook
 | YouTube
 | LinkedIn



On Thu, Feb 11, 2021 at 9:33 PM Rob Sargent  wrote:

> DBA=~super user
> If your clients have dba privs you need separate (vertical)servers
>
>
>

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

[Rob Sargent]

DBA=~super user
If your clients have dba privs you need separate (vertical)servers

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

[Jagmohan Kaintura]

Hi Stephen,

Note that views aren't actually guaranteed to provide the isolation
you're looking for unless you mark them as being a security barrier,
see: https://www.postgresql.org/docs/current/rules-privileges.html

By using Security Barrier we had a huge impact on performance  , it was not
considering proper indexes and was doing some filtration with respect to
that User on top of other filtration. So we didn't choose to add a security
barrier with each view we created.

Similar issue we had with ROW level security enabling, tables were always
going for Sequential Scan, when policies were imposed on rows.

>From an implementation perspective, I thought I asked questions from bottom
to top.

>From a Development perspective we are moving our already multi-tenant
system (achieved at database level with views) to SaaS implementation . In
SaaS we have tried to achieve isolation to bit extend , but now we wanted
to have encryption for multiple tenants .

So as over from all these discussions best would be to achieve encryption
at application level only.

But not sure how we can Limit access of DBA's across tenants. We dont want
DBA's of one customer accessing or viewing data to another customer. Or
Overall DBA's shouldn't be able to access sensitive data from database.

We are mostly looking  over Insider Thread... Application Server
Compromise..DB server Compromise.




On Fri, Feb 12, 2021 at 1:29 AM Stephen Frost  wrote:

> Greetings,
>
> * Jagmohan Kaintura (jagmo...@tecorelabs.com) wrote:
> > Yup right now data is being accessed in this manner only.
> > application access using tenant user only who have specific tenantId in
> > that session and can see its own data only. It doesn't know about anyone
> > else's data and neither can get/fetch.
> >
> > So isolation is 100% guaranteed right now.
>
> Note that views aren't actually guaranteed to provide the isolation
> you're looking for unless you mark them as being a security barrier,
> see: https://www.postgresql.org/docs/current/rules-privileges.html
>
> Alternatively, you could use RLS and CREATE POLICY:
>
> https://www.postgresql.org/docs/current/ddl-rowsecurity.html
>
> > But isolation is not enough from an operations perspective, so I need
> > encryption too in some way or another way, whatever postgreSQL supports
> > and  encryption key should differ for  a tenant .
>
> You can have PG do encryption by using the pgcrypto extension, perhaps
> with some custom GUC and views (which should really also be security
> barrier..) to have it be transparent.  As mentioned elsewhere, you're
> really better off doing it in the application though, so that the DB
> server doesn't ever see the plaintext data.  You should really be
> considering what the attack vector you're concerned about is though-
> SQL injection?  Insider threat?  Improper media disposal?  Application
> server compromise?  DB server compromise?  etc.
>
> Thanks,
>
> Stephen
>


-- 
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.

Re: Consequence of changes to CTE's in 12

[Steve Baldwin]

Thanks Tom. This optimization fences concept is a new one to me, so great
to know about.

This does indeed give me a nice version-independent solution, and make me a
very happy camper ;-)

Steve

On Fri, Feb 12, 2021 at 11:45 AM Tom Lane  wrote:

> Steve Baldwin  writes:
> > Is there a chance that the query optimiser should 'notice' the
> > pg_try_advisory_xact_lock function, and not be so clever when it sees it?
>
> The general policy with respect to volatile functions in WHERE quals is
> "here be dragons".  You don't have enough control over when a WHERE clause
> will be evaluated to be sure about what the semantics will be; and we
> don't want to tie the optimizer's hands to the extent that would be needed
> to make it fully predictable.
>
> In this particular case, you can make it fairly safe by making sure there
> are optimization fences both above and below where the WHERE clause is.
> You have one above from the LIMIT 1, but (with the new interpretation of
> CTEs) not one below it.  Adding a fence -- either OFFSET 0 or LIMIT ALL --
> to the first CTE should fix it in a reasonably version-independent
> fashion.
>
> regards, tom lane
>

Re: Consequence of changes to CTE's in 12

[Tom Lane]

Steve Baldwin  writes:
> Is there a chance that the query optimiser should 'notice' the
> pg_try_advisory_xact_lock function, and not be so clever when it sees it?

The general policy with respect to volatile functions in WHERE quals is
"here be dragons".  You don't have enough control over when a WHERE clause
will be evaluated to be sure about what the semantics will be; and we
don't want to tie the optimizer's hands to the extent that would be needed
to make it fully predictable.

In this particular case, you can make it fairly safe by making sure there
are optimization fences both above and below where the WHERE clause is.
You have one above from the LIMIT 1, but (with the new interpretation of
CTEs) not one below it.  Adding a fence -- either OFFSET 0 or LIMIT ALL --
to the first CTE should fix it in a reasonably version-independent
fashion.

regards, tom lane

Re: Consequence of changes to CTE's in 12

[Steve Baldwin]

Thanks all. The fact that this is a view is not really relevant. I only
bundled as a view here to make testing simpler. The underlying query still
behaves differently pre-12 and 12+.

Is there a chance that the query optimiser should 'notice' the
pg_try_advisory_xact_lock function, and not be so clever when it sees it?

It makes me wonder what other queries we might have that are inadvertently
relying on the default materializing behaviour of pre-12.

Steve

On Fri, Feb 12, 2021 at 11:24 AM Michael Lewis  wrote:

> This functionality seems more a candidate for a set-returning function
> rather than a view, but I like my views to be side effect free and read
> only. It would be trivial to implement in plpgsql I believe.
>
> If you move the limit 1 to the first CTE, does it not give you the same
> behavior in both versions?
>
>>

Re: Consequence of changes to CTE's in 12

[Tom Lane]

Michael Lewis  writes:
> If you move the limit 1 to the first CTE, does it not give you the same
> behavior in both versions?

Not sure if that's exactly the same, but certainly adding a traditional
optimization fence (OFFSET 0) to the first CTE should do the trick.

regards, tom lane

Re: Consequence of changes to CTE's in 12

[Michael Lewis]

This functionality seems more a candidate for a set-returning function
rather than a view, but I like my views to be side effect free and read
only. It would be trivial to implement in plpgsql I believe.

If you move the limit 1 to the first CTE, does it not give you the same
behavior in both versions?

>

Re: Consequence of changes to CTE's in 12

[David G. Johnston]

On Thu, Feb 11, 2021 at 5:07 PM Steve Baldwin 
wrote:

> My 'dilemma' is that this functionality is packaged and the database it is
> bundled into could be running on a pre-12 version or 12+. Is there any way
> I can rewrite my view to achieve the same outcome (i.e. only creating 0 or
> 1 advisory locks) regardless of the server version? I realise I could have
> two installation scripts but if it is installed into a pre-12 DB and that
> DB is subsequently upgraded to 12+, my behaviour is broken.
>

Pretty sure you will need to choose a location in which to make the
installation behave version-dependently.  Within PostgreSQL itself that
would be most easily done by writing a pl/pgsql function that conditionally
adds the MATERIALIZED indicator on the textual representation of the query
before executing it.  You can hide that function call within a view if
desired.

David J.

Consequence of changes to CTE's in 12

[Steve Baldwin]

Hi,

I realise this is probably an edge case, but would appreciate some advice
or suggestions.

I have a table that has rows to be processed:

postgres=# create table lock_test (id uuid primary key default
gen_random_uuid(), lock_id bigint);
CREATE TABLE
postgres=# insert into lock_test (lock_id) values
(10),(10),(20),(30),(30),(30);
INSERT 0 6
postgres=#* select * from lock_test;
  id  | lock_id
--+-
 326a2d34-ecec-4c01-94bb-40f43f244d40 |  10
 8ed1d680-6304-4fb2-a47c-9427c6d48622 |  10
 04482ba1-7193-4e7f-a507-71fe6a351781 |  20
 34003468-e959-4c7b-a48c-97195c43982e |  30
 2d20394b-c79b-4867-8d0a-72044c370543 |  30
 bef7b880-e7a0-4c07-8eab-182c9c1bd33a |  30
(6 rows)

My business rule says I need to process rows by lock_id in descending order
of the number of rows. In my test data, that would mean rows with a lock_id
of 30 would be processed first.

If another 'processor' wakes up while lock_id 30 is being processed, it
moves on to lock_id 10, etc.

My pre-12 solution was a view something like this:

postgres=# create or replace view lock_test_v
as
with g as (
  select lock_id, count(*) as n_rows
  from lock_test
  group by lock_id
  order by n_rows desc
  ), l as (
  select lock_id
  from g
  where pg_try_advisory_xact_lock(lock_id)
  limit 1)
select t.*
from lock_test as t
join l on t.lock_id = l.lock_id
;
CREATE VIEW

This works fine, and only creates one advisory lock (or zero) when querying
the view:

postgres=# begin;
BEGIN
postgres=#* select classid, objid from pg_locks where locktype = 'advisory'
and pid = pg_backend_pid();
 classid | objid
-+---
(0 rows)

postgres=#* select * from lock_test_v;
  id  | lock_id
--+-
 34003468-e959-4c7b-a48c-97195c43982e |  30
 2d20394b-c79b-4867-8d0a-72044c370543 |  30
 bef7b880-e7a0-4c07-8eab-182c9c1bd33a |  30
(3 rows)

postgres=#* select classid, objid from pg_locks where locktype = 'advisory'
and pid = pg_backend_pid();
 classid | objid
-+---
   0 |30
(1 row)

However in 12, the same view returns the same data, but generates multiple
advisory locks:

sns_publisher=# begin;
BEGIN
sns_publisher=#* select version();
version
---
 PostgreSQL 12.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine
10.2.1_pre1) 10.2.1 20201203, 64-bit
(1 row)

sns_publisher=#* select classid, objid from pg_locks where locktype =
'advisory' and pid = pg_backend_pid();
 classid | objid
-+---
(0 rows)

sns_publisher=#* select * from lock_test_v;
  id  | lock_id
--+-
 1a9f3f77-fcdc-4779-8fd9-30f274825e15 |  30
 ac670997-9c23-44da-8eb8-e055f02a5f19 |  30
 b5f939ac-7c7d-4975-811a-9af26aaa3a31 |  30
(3 rows)

sns_publisher=#* select classid, objid from pg_locks where locktype =
'advisory' and pid = pg_backend_pid();
 classid | objid
-+---
   0 |20
   0 |30
   0 |10
(3 rows)

If I use 'as materialized' for my 'g' cte, I get the same outcome as with
pre-12 versions.

My 'dilemma' is that this functionality is packaged and the database it is
bundled into could be running on a pre-12 version or 12+. Is there any way
I can rewrite my view to achieve the same outcome (i.e. only creating 0 or
1 advisory locks) regardless of the server version? I realise I could have
two installation scripts but if it is installed into a pre-12 DB and that
DB is subsequently upgraded to 12+, my behaviour is broken.

Any suggestions greatly appreciated.

Steve

Re: Compiler warnings on Debian 10

[Tom Lane]

Looking at things more closely, I realize that the warnings are not
showing up in most compiles (if they were, you'd have many more of
them).  They are only showing up in the llvmjit_*.cpp files, which
need to be built with a C++ compiler not gcc.  I can see in your
make log that those are getting built with "c++", so now I wonder
exactly which compiler that is.

You could probably suppress these complaints by passing "CXX=g++"
to configure.  Or at least, you could if g++ was installed, but
I think configure will default to that if it can find it...

regards, tom lane

Re: Compiler warnings on Debian 10

[Daniel Westermann (DWE)]

>> Nothing special. Configure and make log attached. The same procedure works 
>> fine on my local Debian 10.7.

>Quite odd.  The configure output clearly shows that it thought gnu_printf
>is fine:

>checking for printf format archetype... gnu_printf

>Possibly the corresponding section of config.log would provide more
>insight.

Please find it attached.

Regards
Daniel

config.log.gz
Description: config.log.gz

Re: Unable to execute Query in parallel for partitioned table

[Brajendra Pratap]

Hi Albe,

We have checked as per your suggestion and we are good now.

Thank you !!!


On Thu, 11 Feb, 2021, 8:49 PM Brajendra Pratap, <
brajendra.pratap...@gmail.com> wrote:

> Hi Albe,
>
> Thank you so much for information, will check this and get back to you if
> any help required.
>
> I have a doubt why didn't the parallelism works here ,could u plz guide me?
>
> Thank you so much again.
>
> On Thu, 11 Feb, 2021, 1:23 PM Laurenz Albe, 
> wrote:
>
>> On Thu, 2021-02-11 at 05:09 +0530, Brajendra Pratap wrote:
>> > I am unable to execute the below in parallel plz suggest how can I
>> achieve parallelism here.
>> >
>> > select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER()  OVER (order
>> by trn_transaction_date desc ) AS RowNumber from (
>> > select * from transactions where trn_store_date_id=20201202) abc;
>> >
>> > Query plan is as mentioned below :-
>> >
>> > explain analyze select count(*) over ()
>> VIEWALLROWCOUNT,abc.*,ROW_NUMBER()  OVER (order by trn_transaction_date
>> desc ) AS RowNumber from (
>> > select * from transactions where trn_store_date_id=20201218) abc;
>> > LOG:  duration: 25820.176 ms  statement: explain analyze select
>> count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER()  OVER (order by
>> trn_transaction_date desc ) AS RowNumber from (
>> > select * from transactions where trn_store_date_id=20201218) abc;
>> >
>>QUERY PLAN
>> >
>> --
>> >  WindowAgg  (cost=4474843.51..4498066.81 rows=774110 width=21297)
>> (actual time=21455.495..25241.738 rows=795190 loops=1)
>> >->  WindowAgg  (cost=4474843.51..4488390.44 rows=774110 width=21289)
>> (actual time=10588.494..15311.865 rows=795190 loops=1)
>> >  ->  Sort  (cost=4474843.51..4476778.79 rows=774110
>> width=21281) (actual time=10588.422..11771.300 rows=795190 loops=1)
>> >Sort Key: transactions.trn_transaction_date DESC
>> >Sort Method: external merge  Disk: 1496856kB
>> >->  Result  (cost=0.00..270640.32 rows=774110
>> width=21281) (actual time=0.117..4504.159 rows=795190 loops=1)
>> >  ->  Append  (cost=0.00..262899.22 rows=774110
>> width=21281) (actual time=0.094..1449.532 rows=795190 loops=1)
>> >->  Seq Scan on transactions
>> (cost=0.00..0.00 rows=1 width=47554) (actual time=0.019..0.019 rows=0
>> loops=1)
>> >  Filter: (trn_store_date_id = 20201218)
>> >->  Index Scan using
>> idx_202012_trn_store_date_id on transactions_202012  (cost=0.56..259028.67
>> rows=774109 width=21281) (actual time=0.074..1357.764 rows=795190 loops=1)
>> >  Index Cond: (trn_store_date_id =
>> 20201218)
>> >  Planning Time: 116.472 ms
>> >  Execution Time: 25676.098 ms
>> >
>> > Note :- We had tried different options like max_worker_processes,
>> max_parallel_workers,
>> max_parallel_workers_per_gather,max_parallel_maintenance_worker to execute
>> it in parallel but no luck.
>>
>> I don't think parallelization will help you here.
>>
>> Your problem is probably the "abc.*" in the SELECT list.
>>
>> There must be really large data in this table, so it takes a long time to
>> fetch and
>> sort the rows.  Try selecting only the columns you need.
>>
>> Alternatively, add a LIMIT clause.  Do you really need all 80 rows?
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>>

Re: Compiler warnings on Debian 10

[Tom Lane]

"Daniel Westermann (DWE)"  writes:
> "Daniel Westermann (DWE)"  writes:
>>> I was wondering if someone already has seen these warnings on Debian 10 
>>> (PostgreSQL 13.1):

>>> ../../../../src/include/port.h:176:70: warning: 'format' attribute argument 
>>> not supported: gnu_printf
>>>    [-Wignored-attributes]

>> Huh.  What compiler are you using, exactly?  

> Nothing special. Configure and make log attached. The same procedure works 
> fine on my local Debian 10.7.

Quite odd.  The configure output clearly shows that it thought gnu_printf
is fine:

checking for printf format archetype... gnu_printf

Possibly the corresponding section of config.log would provide more
insight.

regards, tom lane

Re: Problem with trigger function

[David G. Johnston]

On Thursday, February 11, 2021, Steve Baldwin 
wrote:

> David, from what I can see of the docs, for 9.6 it is PROCEDURE.  It seems
> FUNCTION didn't appear until 11.
>

Indeed.  I didn’t pay attention to the version.

David J.

Re: Problem with trigger function

[Steve Baldwin]

David, from what I can see of the docs, for 9.6 it is PROCEDURE.  It seems
FUNCTION didn't appear until 11.

Steve

On Fri, Feb 12, 2021 at 7:05 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
> On Thursday, February 11, 2021, Steve Baldwin 
> wrote:
>
>> Try ... EXECUTE PROCEDURE customer_num_informix()
>>
>
>
> FUNCTION, not PROCEDURE
>
> David J.
>

Re: Problem with trigger function

[David G. Johnston]

On Thursday, February 11, 2021, Steve Baldwin 
wrote:

> Try ... EXECUTE PROCEDURE customer_num_informix()
>


FUNCTION, not PROCEDURE

David J.

Re: Problem with trigger function

[Dave Cramer]

FWIW, messing with serial numbers like this is pretty risky.Sequences have
transactional semantics for a reason.

Dave Cramer
www.postgres.rocks


On Thu, 11 Feb 2021 at 14:57, Steve Baldwin  wrote:

> Try ... EXECUTE PROCEDURE customer_num_informix()
>
> Steve
>
> On Fri, Feb 12, 2021 at 6:47 AM James B. Byrne 
> wrote:
>
>> I am trying to implement a trigger in a PostgreSQL-9.6.17 database:
>>
>> CREATE OR REPLACE FUNCTION customer_num_informix()
>>   RETURNS trigger AS $$
>> BEGIN
>>   -- An Aubit/Informix 4GL INSERT statement passes the value 0 to Informix
>>   -- DBMS for columns that have the SERIAL data type.  Informix will then
>>   -- use the incremented serial number in place of 0. PostgreSQL instead
>>   -- will simply take the value 0 and replace the incremented serial
>> number.
>>   -- This trigger function emulates the Informix DBMS behaviour.
>>   --
>>   -- The NEW variable contains the data for the row to be INSERTed or
>>   -- UPDATEd. It, and its complement OLD used for UPDATE and DELETE,
>>   -- are automatically created and populated by PostgreSQL whenever
>>   -- a data-change function is called.
>>   --
>>   IF NEW.customer_num = 0 THEN
>> SELECT nextval('customer_customer_num_seq') INTO
>> NEW.customer_customer_num;
>>   ELSE
>> IF NEW.customer_customer_num > 0 THEN
>>   PERFORM setval('customer_customer_num_seq',
>> NEW.customer_customer_num);
>> END IF;
>>   END IF;
>>   RETURN NEW;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> ALTER FUNCTION public.customer_num_informix() OWNER TO hll_4gl_testing;
>>
>> CREATE TRIGGER customer_num_serial
>>   BEFORE INSERT ON customer
>> FOR EACH ROW EXECUTE customer_num_informix();
>>
>> The problem is that I am getting a syntax error on the CREATE TRIGGER
>> statement:
>>
>> ERROR:  syntax error at or near "customer_num_informix"
>> LINE 3: FOR EACH ROW EXECUTE customer_num_informix();
>>
>> I do not see what the error is. What is wrong with the syntax I used?
>>
>> --
>> ***  e-Mail is NOT a SECURE channel  ***
>> Do NOT transmit sensitive data via e-Mail
>>Unencrypted messages have no legal claim to privacy
>>  Do NOT open attachments nor follow links sent by e-Mail
>>
>> James B. Byrnemailto:byrn...@harte-lyne.ca
>> Harte & Lyne Limited  http://www.harte-lyne.ca
>> 9 Brockley Drive  vox: +1 905 561 1241
>> Hamilton, Ontario fax: +1 905 561 0757
>> Canada  L8E 3C3
>>
>>
>>
>>

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

[Stephen Frost]

Greetings,

* Jagmohan Kaintura (jagmo...@tecorelabs.com) wrote:
> Yup right now data is being accessed in this manner only.
> application access using tenant user only who have specific tenantId in
> that session and can see its own data only. It doesn't know about anyone
> else's data and neither can get/fetch.
> 
> So isolation is 100% guaranteed right now.

Note that views aren't actually guaranteed to provide the isolation
you're looking for unless you mark them as being a security barrier,
see: https://www.postgresql.org/docs/current/rules-privileges.html

Alternatively, you could use RLS and CREATE POLICY:

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

> But isolation is not enough from an operations perspective, so I need
> encryption too in some way or another way, whatever postgreSQL supports
> and  encryption key should differ for  a tenant .

You can have PG do encryption by using the pgcrypto extension, perhaps
with some custom GUC and views (which should really also be security
barrier..) to have it be transparent.  As mentioned elsewhere, you're
really better off doing it in the application though, so that the DB
server doesn't ever see the plaintext data.  You should really be
considering what the attack vector you're concerned about is though-
SQL injection?  Insider threat?  Improper media disposal?  Application
server compromise?  DB server compromise?  etc.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Problem with trigger function

[Steve Baldwin]

Try ... EXECUTE PROCEDURE customer_num_informix()

Steve

On Fri, Feb 12, 2021 at 6:47 AM James B. Byrne 
wrote:

> I am trying to implement a trigger in a PostgreSQL-9.6.17 database:
>
> CREATE OR REPLACE FUNCTION customer_num_informix()
>   RETURNS trigger AS $$
> BEGIN
>   -- An Aubit/Informix 4GL INSERT statement passes the value 0 to Informix
>   -- DBMS for columns that have the SERIAL data type.  Informix will then
>   -- use the incremented serial number in place of 0. PostgreSQL instead
>   -- will simply take the value 0 and replace the incremented serial
> number.
>   -- This trigger function emulates the Informix DBMS behaviour.
>   --
>   -- The NEW variable contains the data for the row to be INSERTed or
>   -- UPDATEd. It, and its complement OLD used for UPDATE and DELETE,
>   -- are automatically created and populated by PostgreSQL whenever
>   -- a data-change function is called.
>   --
>   IF NEW.customer_num = 0 THEN
> SELECT nextval('customer_customer_num_seq') INTO
> NEW.customer_customer_num;
>   ELSE
> IF NEW.customer_customer_num > 0 THEN
>   PERFORM setval('customer_customer_num_seq',
> NEW.customer_customer_num);
> END IF;
>   END IF;
>   RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> ALTER FUNCTION public.customer_num_informix() OWNER TO hll_4gl_testing;
>
> CREATE TRIGGER customer_num_serial
>   BEFORE INSERT ON customer
> FOR EACH ROW EXECUTE customer_num_informix();
>
> The problem is that I am getting a syntax error on the CREATE TRIGGER
> statement:
>
> ERROR:  syntax error at or near "customer_num_informix"
> LINE 3: FOR EACH ROW EXECUTE customer_num_informix();
>
> I do not see what the error is. What is wrong with the syntax I used?
>
> --
> ***  e-Mail is NOT a SECURE channel  ***
> Do NOT transmit sensitive data via e-Mail
>Unencrypted messages have no legal claim to privacy
>  Do NOT open attachments nor follow links sent by e-Mail
>
> James B. Byrnemailto:byrn...@harte-lyne.ca
> Harte & Lyne Limited  http://www.harte-lyne.ca
> 9 Brockley Drive  vox: +1 905 561 1241
> Hamilton, Ontario fax: +1 905 561 0757
> Canada  L8E 3C3
>
>
>
>

Problem with trigger function

[James B. Byrne]

I am trying to implement a trigger in a PostgreSQL-9.6.17 database:

CREATE OR REPLACE FUNCTION customer_num_informix()
  RETURNS trigger AS $$
BEGIN
  -- An Aubit/Informix 4GL INSERT statement passes the value 0 to Informix
  -- DBMS for columns that have the SERIAL data type.  Informix will then
  -- use the incremented serial number in place of 0. PostgreSQL instead
  -- will simply take the value 0 and replace the incremented serial number.
  -- This trigger function emulates the Informix DBMS behaviour.
  --
  -- The NEW variable contains the data for the row to be INSERTed or
  -- UPDATEd. It, and its complement OLD used for UPDATE and DELETE,
  -- are automatically created and populated by PostgreSQL whenever
  -- a data-change function is called.
  --
  IF NEW.customer_num = 0 THEN
SELECT nextval('customer_customer_num_seq') INTO NEW.customer_customer_num;
  ELSE
IF NEW.customer_customer_num > 0 THEN
  PERFORM setval('customer_customer_num_seq', 
NEW.customer_customer_num);
END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION public.customer_num_informix() OWNER TO hll_4gl_testing;

CREATE TRIGGER customer_num_serial
  BEFORE INSERT ON customer
FOR EACH ROW EXECUTE customer_num_informix();

The problem is that I am getting a syntax error on the CREATE TRIGGER statement:

ERROR:  syntax error at or near "customer_num_informix"
LINE 3: FOR EACH ROW EXECUTE customer_num_informix();

I do not see what the error is. What is wrong with the syntax I used?

-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: Compiler warnings on Debian 10

[Tom Lane]

"Daniel Westermann (DWE)"  writes:
> I was wondering if someone already has seen these warnings on Debian 10 
> (PostgreSQL 13.1):

> ../../../../src/include/port.h:176:70: warning: 'format' attribute argument 
> not supported: gnu_printf
>   [-Wignored-attributes]

Huh.  What compiler are you using, exactly?  Perhaps you used a different
compiler when running configure (or are trying to re-use configure results
from some other machine)?

regards, tom lane

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

[Thorsten Schöning]

Guten Tag Guy Burgess,
am Donnerstag, 11. Februar 2021 um 01:21 schrieben Sie:

> What appears to be happening is the affected WAL files (which is
> usually only 2 or 3 WAL files at a time) are somehow "losing" their
> NTFS permissions, so the PG process can't rename them - though of
> course the PG process created them. Even running icacls as admin
> gives "Access is denied" on those files. A further oddity is the
> affected files do end up disappearing after a while.

If you see that somewhat frequently, use Process Monitor and Process
Explorer to see who accesses those files how. ProcExp easily allows
you to find all open handles per file. If it's not AV, it might be
something like Windows Search Indexer as well, if that is enabled by
default in Server 2019 at all.

Though, even with my Windows 10 and Search Indexer enabled I didn't
run into such problems yet. And especially when rewriting large parts
of my databases with lots of created WAL files, I see the Indexer
working on those files, but NOT conflicting with Postgres yet.

The behaviour you describe happens exactly when two processes e.g.
concurrently hold HANDLEs on the same file and one of those deletes
the file then. Windows keeps file names until all open HANDLEs are
closed and depending on how those HANDLEs have been opened by the
first app, concurrent deletion is perferctly fine for Windows.

Though, a such deleted file can't be opened easily anymore and looks
like it has lost permissions only. But that's not the case, it's
deleted already. It might be that this happens for Postgres to itself
somehow when some other app has an open HANDLE. I don't think that
some other app is deleting that file by purpose instead, reading it
for some reason seems more likely to me.

> dwShareMode
> FILE_SHARE_DELETE

> Enables subsequent open operations on a file or device to request
> delete access. Otherwise, other processes cannot open the file or
> device if they request delete access.

https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-createfilew

Mit freundlichen Grüßen

Thorsten Schöning

-- 
Thorsten Schöning
AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore 
Gruppe - Ihr Full-Service-Dienstleister für IT und TK

E-Mail: thorsten.schoen...@am-soft.de
Web:http://www.AM-SoFT.de/

Telefon: 05151-  9468- 0
Telefon: 05151-  9468-55
Fax: 05151-  9468-88
Mobil:0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH i.G., Brandenburger Str. 7c, 31789 
Hameln
AG Hannover HRB neu - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.

Mit freundlichen Grüßen

Thorsten Schöning


Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil: 
Webseite: https://www.am-soft.de 

AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore 
Gruppe - Ihr Full-Service-Dienstleister für IT und TK

AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0

Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80

CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1

Büro Dallgow-Döberitz
Tel: 03322 507 020

Büro Kloster Lehnin
Tel: 033207 566 530

PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0

Büro Neuruppin
Tel: 033932 606 090

ACI EDV Systemhaus Dresden GmbH
Dresden
Tel: 0351 254 410

Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0

Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7

Büro Liebenwalde
Tel: 033054 810 00

HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97

Bitstore IT – Consulting GmbH
NL Piesteritz 
Piesteritz
Tel: 03491 644 868 6

Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0

MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3

Firmensitz: MF Computer Service - Bitstore Gütersloh GmbH, Gildestraße 25, 
33442 Herzebrock-Clarholz
Geschäftsführer Janine Galonska

Re: Which partition scheme makes sense for my time based IoT-datagrams?

[Thorsten Schöning]

Guten Tag Peter J. Holzer,
am Donnerstag, 11. Februar 2021 um 15:03 schrieben Sie:

> If you get one datagram per day, how it is useful to query all datagrams
> received in the last 15 minutes?[...]

There's a special installation mode during which users are setting up
their IoT-devices. During that mode datagrams might be received more
often, while newer ones are still deleting older ones. 15 minutes is
simply the default value in the corresponding UI to check if anything
has been received properly, but users might send+check more often.

15 days is some different default value regarding monitoring
IoT-devices, if they have been received at all and stuff like that. 15
months again is a value users are interested in, because they need
one measuring value per month most likely.

And 3 times 15 simply "looks" good as well... :-)

Mit freundlichen Grüßen

Thorsten Schöning

-- 
Thorsten Schöning
AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore 
Gruppe - Ihr Full-Service-Dienstleister für IT und TK

E-Mail: thorsten.schoen...@am-soft.de
Web:http://www.AM-SoFT.de/

Telefon: 05151-  9468- 0
Telefon: 05151-  9468-55
Fax: 05151-  9468-88
Mobil:0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH i.G., Brandenburger Str. 7c, 31789 
Hameln
AG Hannover HRB neu - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.

Mit freundlichen Grüßen

Thorsten Schöning


Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil: 
Webseite: https://www.am-soft.de 

AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore 
Gruppe - Ihr Full-Service-Dienstleister für IT und TK

AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0

Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80

CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1

Büro Dallgow-Döberitz
Tel: 03322 507 020

Büro Kloster Lehnin
Tel: 033207 566 530

PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0

Büro Neuruppin
Tel: 033932 606 090

ACI EDV Systemhaus Dresden GmbH
Dresden
Tel: 0351 254 410

Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0

Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7

Büro Liebenwalde
Tel: 033054 810 00

HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97

Bitstore IT - Consulting GmbH
NL Piesteritz 
Piesteritz
Tel: 03491 644 868 6

Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0

MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3

Firmensitz: MF Computer Service - Bitstore Gütersloh GmbH, Gildestraße 25, 
33442 Herzebrock-Clarholz
Geschäftsführer Janine Galonska

Re: Unable to execute Query in parallel for partitioned table

[Brajendra Pratap]

Hi Albe,

Thank you so much for information, will check this and get back to you if
any help required.

I have a doubt why didn't the parallelism works here ,could u plz guide me?

Thank you so much again.

On Thu, 11 Feb, 2021, 1:23 PM Laurenz Albe, 
wrote:

> On Thu, 2021-02-11 at 05:09 +0530, Brajendra Pratap wrote:
> > I am unable to execute the below in parallel plz suggest how can I
> achieve parallelism here.
> >
> > select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER()  OVER (order
> by trn_transaction_date desc ) AS RowNumber from (
> > select * from transactions where trn_store_date_id=20201202) abc;
> >
> > Query plan is as mentioned below :-
> >
> > explain analyze select count(*) over ()
> VIEWALLROWCOUNT,abc.*,ROW_NUMBER()  OVER (order by trn_transaction_date
> desc ) AS RowNumber from (
> > select * from transactions where trn_store_date_id=20201218) abc;
> > LOG:  duration: 25820.176 ms  statement: explain analyze select count(*)
> over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER()  OVER (order by
> trn_transaction_date desc ) AS RowNumber from (
> > select * from transactions where trn_store_date_id=20201218) abc;
> >
>  QUERY PLAN
> >
> --
> >  WindowAgg  (cost=4474843.51..4498066.81 rows=774110 width=21297)
> (actual time=21455.495..25241.738 rows=795190 loops=1)
> >->  WindowAgg  (cost=4474843.51..4488390.44 rows=774110 width=21289)
> (actual time=10588.494..15311.865 rows=795190 loops=1)
> >  ->  Sort  (cost=4474843.51..4476778.79 rows=774110 width=21281)
> (actual time=10588.422..11771.300 rows=795190 loops=1)
> >Sort Key: transactions.trn_transaction_date DESC
> >Sort Method: external merge  Disk: 1496856kB
> >->  Result  (cost=0.00..270640.32 rows=774110
> width=21281) (actual time=0.117..4504.159 rows=795190 loops=1)
> >  ->  Append  (cost=0.00..262899.22 rows=774110
> width=21281) (actual time=0.094..1449.532 rows=795190 loops=1)
> >->  Seq Scan on transactions
> (cost=0.00..0.00 rows=1 width=47554) (actual time=0.019..0.019 rows=0
> loops=1)
> >  Filter: (trn_store_date_id = 20201218)
> >->  Index Scan using
> idx_202012_trn_store_date_id on transactions_202012  (cost=0.56..259028.67
> rows=774109 width=21281) (actual time=0.074..1357.764 rows=795190 loops=1)
> >  Index Cond: (trn_store_date_id =
> 20201218)
> >  Planning Time: 116.472 ms
> >  Execution Time: 25676.098 ms
> >
> > Note :- We had tried different options like max_worker_processes,
> max_parallel_workers,
> max_parallel_workers_per_gather,max_parallel_maintenance_worker to execute
> it in parallel but no luck.
>
> I don't think parallelization will help you here.
>
> Your problem is probably the "abc.*" in the SELECT list.
>
> There must be really large data in this table, so it takes a long time to
> fetch and
> sort the rows.  Try selecting only the columns you need.
>
> Alternatively, add a LIMIT clause.  Do you really need all 80 rows?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Which partition scheme makes sense for my time based IoT-datagrams?

[Peter J. Holzer]

On 2021-02-10 16:09:30 +0100, Thorsten Schöning wrote:
> I have a table storing datagrams from some IoT-devices, with one
> datagram per device per day most likely for around 75'000 devices
> currently. I want to test query performance with a partitioned table
> and am interested in the following queries mostly: 
> 
> * querying arbitrary datagrams by their ID
> * querying datagrams being X old based on some timestamp
>   * 15 minutes

If you get one datagram per day, how it is useful to query all datagrams
received in the last 15 minutes? That's either a random sample (if
devices report at random times) or empty (if they all report at midnight
and it isn't just after midnight).

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: SV: Insertion time is very high for inserting data in postgres

[Peter J. Holzer]

On 2021-02-10 11:10:41 +, Niels Jespersen wrote:
> >Fra: prachi surangalikar 
> >We are using Postgres 12.2.1 for fetching per minute data for about 25
> machines but running parallely via a single thread in python.
> >But suddenly the insertion time has increased to a very high level, about 30
>> second for one machine.
> 
> >We are in so much problem as the data fetching is becoming slow.
> 
> >if anyone could help us to solve this problem it would be of great help to 
> >us.
> 
> Get your data into a Text.IO memory structure and then use copy https://
> www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from
> 
>  
> 
> This is THE way of high-performant inserts using Postgres.

True, but Prachi wrote that the insert times "suddenly ... increased to a
very high level". It's better to investigate what went wrong than to
blindly make some changes to the code.

As a first measure I would at least turn on statement logging and/or
pg_stat_statements to see which statements are slow, and then
investigate the slow statements further. auto_explain might also be
useful.

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: Assistance on PostgreSQL DB

[Laurenz Albe]

On Thu, 2021-02-11 at 08:57 +, arunkumar.sampathku...@cognizant.com wrote:
> We would like to know the best practice to maintain PostgreSQL DB health so 
> that there is no delay in Jabber chat messages.

You should hire a knowledgeable DBA.

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

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

[Lionel Bouton]

Hi,

Le 11/02/2021 à 01:21, Guy Burgess a écrit :
>
> Hello,
>
> Running 13.1 on Windows Server 2019, I am getting the following log
> entries occasionally:
>
>     2021-02-11 12:34:10.149 NZDT [6072] LOG:  could not rename file
> "pg_wal/0001009900D3": Permission denied
>     2021-02-11 12:40:31.377 NZDT [6072] LOG:  could not rename file
> "pg_wal/0001009900D3": Permission denied
>     2021-02-11 12:46:06.294 NZDT [6072] LOG:  could not rename file
> "pg_wal/0001009900D3": Permission denied
>     2021-02-11 12:46:16.502 NZDT [6072] LOG:  could not rename file
> "pg_wal/0001009900DA": Permission denied
>     2021-02-11 12:50:20.917 NZDT [6072] LOG:  could not rename file
> "pg_wal/0001009900D3": Permission denied
>     2021-02-11 12:50:31.098 NZDT [6072] LOG:  could not rename file
> "pg_wal/0001009900DA": Permission denied
>
> What appears to be happening is the affected WAL files (which is
> usually only 2 or 3 WAL files at a time) are somehow "losing" their
> NTFS permissions, so the PG process can't rename them - though of
> course the PG process created them. Even running icacls as admin gives
> "Access is denied" on those files. A further oddity is the affected
> files do end up disappearing after a while.
>
> The NTFS permissions on the pg_wal directory are correct, and most WAL
> files are unaffected. Chkdsk reports no problems, and the database is
> working fine otherwise. Have tried disabling antivirus software in
> case that was doing something but no difference.
>

I haven't dealt with a Windows environment for quite some time, but from
what I remember an antivirus installs a driver intercepting file
accesses and these drivers are still active even if you disable the
antivirus (I suppose they just call a noop instead of content analysis
code) and can still interfere with your system. For example some years
ago I've seen what looked like a race condition involving rename for
MySQL on Windows that could not be fixed by disabling the antivirus but
could by uninstalling it completely.

You might want to uninstall the antivirus temporarily to check this.

Best regards,

-- 
Lionel Bouton
gérant de JTEK SARL
https://www.linkedin.com/in/lionelbouton/

Assistance on PostgreSQL DB

[ArunKumar.SampathKumar]

Hi Team,

We would like to know the best practice to maintain PostgreSQL DB health so 
that there is no delay in Jabber chat messages.

Regards
Arun

This e-mail and any files transmitted with it are for the sole use of the 
intended recipient(s) and may contain confidential and privileged information. 
If you are not the intended recipient(s), please reply to the sender and 
destroy all copies of the original message. Any unauthorized review, use, 
disclosure, dissemination, forwarding, printing or copying of this email, 
and/or any action taken in reliance on the contents of this e-mail is strictly 
prohibited and may be unlawful. Where permitted by applicable law, this e-mail 
and other e-mail communications sent to and from Cognizant e-mail addresses may 
be monitored.