Re: [GENERAL] RowExclusiveLock timeout while autovacuum

[Sameer Kumar]

On Mon, Aug 15, 2016 at 12:34 PM zh1029  wrote:

> Hi,
>   We are using PostgreSQL 9.3.11. We are observing DB update failed due to
> lock timeout. failure because waiting for RowExclusiveLock.  Autovacuum
> uses
> plain vacuum which uses ShareUpdateExclusiveLock. right?
>

This I learnt recently, if autovacuum (or for that matter a normal vacuum)
identifies that it needs to perform page truncation (release the empty
space or relinquish a complete page back to OS), it would acquire an
Exclusive Lock. I am not very sure if that is a page level lock or a row
level lock (my guess is page level), but it would cause conflict with
read/write queries on the master and would wait for the lock. On standby
this might cause cancellation of queries.

You can simulate this easily with pgbench-

1. Setup a database with scale 1000
2. Create your custom script for pgbench to perform inserts
3. Fire pgbench
4. While pgbench is running the inserts script with multiple clients, you
delete rows from the table where insert is happening. Make sure that
deletes are with range filter large enough to release a few pages (e.g.
where id between 1 and 5000)


Perhaps you might need to -
1. Look into why autovacuum is trying to perform page truncation to often.
In my case I discovered it was a batch-job which would delete almost all
rows and reinsert them, hence creating a huge chunk of free space for
vacuum to relinquish

2. Make sure that the other operations on the database should perform
faster. You may have some scope to tune the PL/pgSQL code or create some
indexes or tune the insert itself



> But from Postgres Manual chapter 13.3. Explicit Locking. both Locks has no
> conflict. So in which situation conflict lock happened. Does autovacuum use
> other lock than ShareUpdateExclusiveLock in certain situation?
>
> Aug 10 15:03:16 DB-1 postgres[3314]: [5-1] DEBUG:  sending cancel to
> blocking autovacuum PID 25047
> Aug 10 15:03:16 DB-1 postgres[3314]: [5-2] DETAIL:  Process 3314 waits for
> RowExclusiveLock on relation 19386 of database 18363.
> Aug 10 15:03:16 DB-1 postgres[3314]: [5-3] CONTEXT:  SQL statement "insert
> into ActiveRadiusSessionTrafficVM7(AccountSessionId)
> values(NEW.AccountSessionId)"
> Aug 10 15:03:16 DB-1 postgres[3314]: [5-4] PL/pgSQL function
> activesessiontriggerfunction() line 22 at SQL statement
> Aug 10 15:03:16 DB-1 postgres[3314]: [5-5] STATEMENT:  INSERT INTO
>
> ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
> ZoneWlanInfo, AppVMInstanceIP)
> values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
> Aug 10 15:03:16 DB-1 postgres[3314]: [6-1] LOG:  process 3314 still waiting
> for RowExclusiveLock on relation 19386 of database 18363 after 1000.186 ms
> Aug 10 15:03:16 DB-1 postgres[3314]: [6-2] CONTEXT:  SQL statement "insert
> into ActiveRadiusSessionTrafficVM7(AccountSessionId)
> values(NEW.AccountSessionId)"
> Aug 10 15:03:16 DB-1 postgres[3314]: [6-3] PL/pgSQL function
> activesessiontriggerfunction() line 22 at SQL statement
> Aug 10 15:03:16 DB-1 postgres[3314]: [6-4] STATEMENT:  INSERT INTO
>
> ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
> ZoneWlanInfo, AppVMInstanceIP)
> values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
> Aug 10 15:03:16 DB-1 postgres[3314]: [7-1] LOG:  process 3314 acquired
> RowExclusiveLock on relation 19386 of database 18363 after 1686.768 ms
> Aug 10 15:03:16 DB-1 postgres[3314]: [7-2] CONTEXT:  SQL statement "insert
> into ActiveRadiusSessionTrafficVM7(AccountSessionId)
> values(NEW.AccountSessionId)"
> Aug 10 15:03:16 DB-1 postgres[3314]: [7-3] PL/pgSQL function
> activesessiontriggerfunction() line 22 at SQL statement
> Aug 10 15:03:16 DB-1 postgres[3314]: [7-4] STATEMENT:  INSERT INTO
>
> ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
> ZoneWlanInfo, AppVMInstanceIP)
> values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
> Aug 10 15:14:52 DB-1 postgres[3314]: [8-1] LOG:  process 3314 still waiting
> for ExclusiveLock on extension of relation 19308 of database 18363 after
> 1000.061 ms
> Aug 10 15:14:52 DB-1 postgres[3314]: [8-2] STATEMENT:  INSERT INTO
>
> ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
> ZoneWlanInfo, AppVMInstanceIP)
> values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
> Aug 10 15:14:52 DB-1 postgres[3314]: [9-1] LOG:  process 3314 acquired
> ExclusiveLock on extension of relation 19308 of database 18363 after
> 1550.529 ms
> Aug 10 15:14:52 DB-1 postgres[3314]: [9-2] STATEMENT:  INSERT INTO
>
> ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Use

[GENERAL] RowExclusiveLock timeout while autovacuum

[zh1029]

Hi,
  We are using PostgreSQL 9.3.11. We are observing DB update failed due to
lock timeout. failure because waiting for RowExclusiveLock.  Autovacuum uses
plain vacuum which uses ShareUpdateExclusiveLock. right?
But from Postgres Manual chapter 13.3. Explicit Locking. both Locks has no
conflict. So in which situation conflict lock happened. Does autovacuum use
other lock than ShareUpdateExclusiveLock in certain situation? 

Aug 10 15:03:16 DB-1 postgres[3314]: [5-1] DEBUG:  sending cancel to
blocking autovacuum PID 25047
Aug 10 15:03:16 DB-1 postgres[3314]: [5-2] DETAIL:  Process 3314 waits for
RowExclusiveLock on relation 19386 of database 18363.
Aug 10 15:03:16 DB-1 postgres[3314]: [5-3] CONTEXT:  SQL statement "insert
into ActiveRadiusSessionTrafficVM7(AccountSessionId)
values(NEW.AccountSessionId)"
Aug 10 15:03:16 DB-1 postgres[3314]: [5-4] PL/pgSQL function
activesessiontriggerfunction() line 22 at SQL statement
Aug 10 15:03:16 DB-1 postgres[3314]: [5-5] STATEMENT:  INSERT INTO
ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
ZoneWlanInfo, AppVMInstanceIP)
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
Aug 10 15:03:16 DB-1 postgres[3314]: [6-1] LOG:  process 3314 still waiting
for RowExclusiveLock on relation 19386 of database 18363 after 1000.186 ms
Aug 10 15:03:16 DB-1 postgres[3314]: [6-2] CONTEXT:  SQL statement "insert
into ActiveRadiusSessionTrafficVM7(AccountSessionId)
values(NEW.AccountSessionId)"
Aug 10 15:03:16 DB-1 postgres[3314]: [6-3] PL/pgSQL function
activesessiontriggerfunction() line 22 at SQL statement
Aug 10 15:03:16 DB-1 postgres[3314]: [6-4] STATEMENT:  INSERT INTO
ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
ZoneWlanInfo, AppVMInstanceIP)
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
Aug 10 15:03:16 DB-1 postgres[3314]: [7-1] LOG:  process 3314 acquired
RowExclusiveLock on relation 19386 of database 18363 after 1686.768 ms
Aug 10 15:03:16 DB-1 postgres[3314]: [7-2] CONTEXT:  SQL statement "insert
into ActiveRadiusSessionTrafficVM7(AccountSessionId)
values(NEW.AccountSessionId)"
Aug 10 15:03:16 DB-1 postgres[3314]: [7-3] PL/pgSQL function
activesessiontriggerfunction() line 22 at SQL statement
Aug 10 15:03:16 DB-1 postgres[3314]: [7-4] STATEMENT:  INSERT INTO
ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
ZoneWlanInfo, AppVMInstanceIP)
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
Aug 10 15:14:52 DB-1 postgres[3314]: [8-1] LOG:  process 3314 still waiting
for ExclusiveLock on extension of relation 19308 of database 18363 after
1000.061 ms
Aug 10 15:14:52 DB-1 postgres[3314]: [8-2] STATEMENT:  INSERT INTO
ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
ZoneWlanInfo, AppVMInstanceIP)
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
Aug 10 15:14:52 DB-1 postgres[3314]: [9-1] LOG:  process 3314 acquired
ExclusiveLock on extension of relation 19308 of database 18363 after
1550.529 ms
Aug 10 15:14:52 DB-1 postgres[3314]: [9-2] STATEMENT:  INSERT INTO
ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
ZoneWlanInfo, AppVMInstanceIP)
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
Aug 10 15:26:16 DB-1 postgres[3314]: [10-1] DEBUG:  sending cancel to
blocking autovacuum PID 26527
Aug 10 15:26:16 DB-1 postgres[3314]: [10-2] DETAIL:  Process 3314 waits for
RowExclusiveLock on relation 19386 of database 18363.
Aug 10 15:26:16 DB-1 postgres[3314]: [10-3] CONTEXT:  SQL statement "insert
into ActiveRadiusSessionTrafficVM7(AccountSessionId)
values(NEW.AccountSessionId)"
Aug 10 15:26:16 DB-1 postgres[3314]: [10-4] PL/pgSQL function
activesessiontriggerfunction() line 22 at SQL statement
Aug 10 15:26:16 DB-1 postgres[3314]: [10-5] STATEMENT:  INSERT INTO
ActiveRadiussession(AccountSessionId,StationMAC,StationIP,StationIPV6,ApMAC,SSID,Username,WlanMAC,ChargeableUserIdentity,NASIPAddress,SessionStartTime,Port,
ZoneWlanInfo, AppVMInstanceIP)
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
Aug 10 15:26:16 DB-1 postgres[3314]: [11-1] LOG:  process 3314 still waiting
for RowExclusiveLock on relation 19386 of database 18363 after 1000.115 ms
Aug 10 15:26:16 DB-1 postgres[3314]: [11-2] CONTEXT:  SQL statement "insert
into ActiveRadiusSessionTrafficVM7(AccountSessionId)
values(NEW.AccountSessionId)"
Aug 10 15:26:16 DB-1 postgres[3314]: [11-3] PL/pgSQL function
activesessiontriggerfunction() line 22 at SQL statement
Aug 10 15:26:16 DB-1 postgres[3314]: [11-4] STATEMENT:  INSERT INTO
Active

Re: [GENERAL] Critical failure of standby

[James Sewell]

Hello All,

The thing which I find a little worrying is that this 'corruption' was
introduced either on the network from PROD -> DR, but then also cascaded to
both other DR servers (either via replication or via archive_command).

Is WAL corruption checked for in any way on standby servers?.

Here is a link to a diagram of the current environment:
http://imgur.com/a/MoKMo

I'll look into patching for a core-dump.


Cheers,

James Sewell,
Solutions Architect



Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000>  *W* www.jirotech.com  *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Sat, Aug 13, 2016 at 5:20 AM, Alvaro Herrera 
wrote:

> James Sewell wrote:
>
> > 2016-08-12 04:43:53 GMT [23614]: [5-1] user=,db=,client=  (0:0)LOG:
> consistent recovery state reached at 3/8811DFF0
> > 2016-08-12 04:43:53 GMT [23614]: [6-1] user=,db=,client=
> (0:XX000)FATAL:  invalid memory alloc request size 3445219328
> > 2016-08-12 04:43:53 GMT [23612]: [3-1] user=,db=,client=  (0:0)LOG:
> database system is ready to accept read only connections
> > 2016-08-12 04:43:53 GMT [23612]: [4-1] user=,db=,client=  (0:0)LOG:
> startup process (PID 23614) exited with exit code 1
> > 2016-08-12 04:43:53 GMT [23612]: [5-1] user=,db=,client=  (0:0)LOG:
> terminating any other active server processes
> > 2016-08-12 04:43:53 GMT [23612]: [6-1] user=,db=,client=  (0:0)LOG:
> archiver process (PID 23627) exited with exit code 1
>
> What version is this?
>
> Hm, so the startup process finds the consistent point (which signals
> postmaster so that line 23612/3 says "ready to accept read-only conns")
> and immediately dies because of the invalid memory alloc error.  I
> suppose that error must be while trying to process some xlog record, but
> without a xlog address it's difficult to say anything.  I suppose you
> could try to pg_xlogdump WAL starting at the last known good address
> 3/8811DFF0 but I wouldn't know what to look for.
>
> One strange thing is that xlog replay sets up an error context, so you
> would have had a line like "xlog redo HEAP" etc, but there's nothing
> here.  So maybe the allocation is not exactly in xlog replay, but
> something different.  We'd need to see a backtrace in order to see what.
> Since this occurs in the startup process, probably the easiest way is to
> patch the source to turn that error into PANIC, then re-run and examine
> the resulting core file.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

-- 

--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

Re: [GENERAL] C++ port of Postgres

[Joy Arulraj]

Kang -- Yes, this is one of the reasons why we chose to do this.

John -- We have not compiled it with non-GCC compilers. But, I presume that
the changes required to support compilation with other compilers should be
minimal as we don't rely on any compiler-specific features.

On Sun, Aug 14, 2016 at 6:30 PM, kang joni  wrote:

> I agree with this project, I dont like any setjmp/longjmp and the
> like. It just fighting against the nature of c++ language. Building
> either from scratch gcc48 or clang381 were easy nowdays on either old
> linux debian squeeze or centos 5. PS: I had this requirement
> circumtances.
>
> On 8/15/16, Joy Arulraj  wrote:
> > Hi Dmitry -- We currently don't use exceptions, but we can certainly use
> > them in the port. We can also use STL and smart pointers to simplify
> > development and minimize memory bugs.
> >
> > On Aug 14, 2016 5:41 PM, "Dmitry Igrishin"  wrote:
> >
> >> Hi Joy,
> >>
> >> 2016-08-15 0:05 GMT+03:00 Joy Arulraj :
> >> > Hi folks --
> >> >
> >> > We have ported Postgres over to the C++ language (C++11 standard).
> >> >
> >> > https://github.com/jarulraj/postgresql-cpp
> >> >
> >> > Our goal is to use certain features of the C++ language and its
> >> > standard
> >> > library to simplify coding, improve code reuse, and avoid bugs.
> Peter's
> >> > article titled `Moving to C++` was a source of inspiration for us.
> >> What about the exceptions? Are you using them?
> >>
> >> --
> >> // Dmitry.
> >>
> >
>

Re: [GENERAL] C++ port of Postgres

[kang joni]

I agree with this project, I dont like any setjmp/longjmp and the
like. It just fighting against the nature of c++ language. Building
either from scratch gcc48 or clang381 were easy nowdays on either old
linux debian squeeze or centos 5. PS: I had this requirement
circumtances.

On 8/15/16, Joy Arulraj  wrote:
> Hi Dmitry -- We currently don't use exceptions, but we can certainly use
> them in the port. We can also use STL and smart pointers to simplify
> development and minimize memory bugs.
>
> On Aug 14, 2016 5:41 PM, "Dmitry Igrishin"  wrote:
>
>> Hi Joy,
>>
>> 2016-08-15 0:05 GMT+03:00 Joy Arulraj :
>> > Hi folks --
>> >
>> > We have ported Postgres over to the C++ language (C++11 standard).
>> >
>> > https://github.com/jarulraj/postgresql-cpp
>> >
>> > Our goal is to use certain features of the C++ language and its
>> > standard
>> > library to simplify coding, improve code reuse, and avoid bugs. Peter's
>> > article titled `Moving to C++` was a source of inspiration for us.
>> What about the exceptions? Are you using them?
>>
>> --
>> // Dmitry.
>>
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C++ port of Postgres

[Joy Arulraj]

Hi Dmitry -- We currently don't use exceptions, but we can certainly use
them in the port. We can also use STL and smart pointers to simplify
development and minimize memory bugs.

On Aug 14, 2016 5:41 PM, "Dmitry Igrishin"  wrote:

> Hi Joy,
>
> 2016-08-15 0:05 GMT+03:00 Joy Arulraj :
> > Hi folks --
> >
> > We have ported Postgres over to the C++ language (C++11 standard).
> >
> > https://github.com/jarulraj/postgresql-cpp
> >
> > Our goal is to use certain features of the C++ language and its standard
> > library to simplify coding, improve code reuse, and avoid bugs. Peter's
> > article titled `Moving to C++` was a source of inspiration for us.
> What about the exceptions? Are you using them?
>
> --
> // Dmitry.
>

Re: [GENERAL] C++ port of Postgres

[Dmitry Igrishin]

Hi Joy,

2016-08-15 0:05 GMT+03:00 Joy Arulraj :
> Hi folks --
>
> We have ported Postgres over to the C++ language (C++11 standard).
>
> https://github.com/jarulraj/postgresql-cpp
>
> Our goal is to use certain features of the C++ language and its standard
> library to simplify coding, improve code reuse, and avoid bugs. Peter's
> article titled `Moving to C++` was a source of inspiration for us.
What about the exceptions? Are you using them?

-- 
// Dmitry.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] C++ port of Postgres

[John R Pierce]

On 8/14/2016 2:05 PM, Joy Arulraj wrote:


We have ported Postgres over to the C++ language (C++11 standard).

https://github.com/jarulraj/postgresql-cpp

Our goal is to use certain features of the C++ language and its 
standard library to simplify coding, improve code reuse, and avoid 
bugs. Peter's article titled `Moving to C++ 
` was a 
source of inspiration for us.


does this compile with the non-GCC compilers that postgres gets built 
with, for instance, IBM XLC++ on AIX, Oracle Studio C++ on Solaris, 
Microsoft Visual C++ on MS Windows, CLang on BSD ?   what about popular 
platforms like RHEL 6, where gcc is 4.4.7 ?




--
john r pierce, recycling bits in santa cruz

[GENERAL] C++ port of Postgres

[Joy Arulraj]

Hi folks --

We have ported Postgres over to the C++ language (C++11 standard).

https://github.com/jarulraj/postgresql-cpp

Our goal is to use certain features of the C++ language and its standard
library to simplify coding, improve code reuse, and avoid bugs. Peter's
article titled `Moving to C++
` was a
source of inspiration for us.

Regards.

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

[Karsten Hilbert]

On Sun, Aug 14, 2016 at 04:02:19PM +0200, Chris Travers wrote:

> One example is of such a service locator is
>  http://search.cpan.org/dist/PGObject-Simple/lib/PGObject/Simple.pm
> 
> It runs as a library which helps the program decide how to do the call.
> Currently it looks in the system catalogs but you still have the ordinal
> syntax too.
> 
> One minor issue currently is that object properties override named
> arguments when it should probably be the other way around.

Aha, now I understand.

Thank you,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

[Chris Travers]

On Sun, Aug 14, 2016 at 3:42 PM, Karsten Hilbert 
wrote:

> Hello Chris,
>
> I am getting closer but ...
>
> > > > Sure.  What I prefer to do is to allow for a (cacheable) lookup on
> the
> > > > basis of some criteria, either:
> > > > 1.  Function name or
> > > > 2.  Function name and first argument type
> > > >
> > > > This assumes that whichever discovery criteria you are using leads to
> > > > uniquely identifying a function.
> > > >
> > > > Then from the argument list, I know the names and types of the
> arguments,
> > > > and the service locator can map them in.  This means:
> > > >
> > > > 1.  You can expose an API which calls arguments by name rather than
> just
> > > > position, and
> > > > 2.  You can add arguments of different types without breaking things
> as
> > > > long as it is agreed that unknown arguments are passed in as NULL.
> >
> > Ok.  Two ways of doing this based on different discovery criteria..  The
> > first would be:
> >
> > CREATE FUNCTION person_save(in_id int, in_first_name text, in_last_name
> > text, in_date_of_birth date)
> > RETURNS person LANGUAGE ... as $$ ... $$;
> >
> > Then you have a service locator
>
> Which is what running where ?
>
> > that says
>
> How ?
>

One example is of such a service locator is
 http://search.cpan.org/dist/PGObject-Simple/lib/PGObject/Simple.pm

It runs as a library which helps the program decide how to do the call.
Currently it looks in the system catalogs but you still have the ordinal
syntax too.

One minor issue currently is that object properties override named
arguments when it should probably be the other way around.

I have a composite type mapper also on CPAN but it is far less mature and
the documentation is not really very good yet.

The basic approach is:

1.  Loop up the argument names
2.  Strop any in_ off the beginning (in_ being a convention used to avoid
name collision with underlying columns)
3.  Map those back in as named arguments or object properties.
4.  Any unknown inputs, we supply NULL (UNKNOWN) to.


>
> Thanks,
> Karsten
>
> >  "I have a person object and want to call person_save."  It then looks
> up the function argument names and
> > calls it something like this:
> >
> > SELECT * FROM  person_save(?, ?, ?, ?)
> >
> > with parameters
> > $object->id, $object->first_name, $object->last_name,
> $object->date_of_birth
>
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

[Karsten Hilbert]

Hello Chris,

I am getting closer but ...

> > > Sure.  What I prefer to do is to allow for a (cacheable) lookup on the
> > > basis of some criteria, either:
> > > 1.  Function name or
> > > 2.  Function name and first argument type
> > >
> > > This assumes that whichever discovery criteria you are using leads to
> > > uniquely identifying a function.
> > >
> > > Then from the argument list, I know the names and types of the arguments,
> > > and the service locator can map them in.  This means:
> > >
> > > 1.  You can expose an API which calls arguments by name rather than just
> > > position, and
> > > 2.  You can add arguments of different types without breaking things as
> > > long as it is agreed that unknown arguments are passed in as NULL.
> 
> Ok.  Two ways of doing this based on different discovery criteria..  The
> first would be:
> 
> CREATE FUNCTION person_save(in_id int, in_first_name text, in_last_name
> text, in_date_of_birth date)
> RETURNS person LANGUAGE ... as $$ ... $$;
> 
> Then you have a service locator

Which is what running where ?

> that says

How ?

Thanks,
Karsten

>  "I have a person object and want to call person_save."  It then looks up the 
> function argument names and
> calls it something like this:
> 
> SELECT * FROM  person_save(?, ?, ?, ?)
> 
> with parameters
> $object->id, $object->first_name, $object->last_name, $object->date_of_birth

-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Chris Travers]

On Sun, Aug 14, 2016 at 12:13 PM, Xtra Coder  wrote:

> Thanks, I'm aware about ability to create temp functions, but this is
> actually too much overhead - I mean unneeded boilerplate code, but it seems
> in current state it is "the least evil" which I have to use.
>
> I think 'what i need' is support for following
> - ability to switch session language from 'sql' to 'pl/pgsql'
> - in that mode - ability to declare session-scope variables, 'DO' is just
> not needed after that
> - SELECTs not targeted into a variable - are written to client output
> - (C) Merlin Moncure - "Ability to embed collection of statements in the
> database under a name and invoke those statements via CALL , which
> does not automatically create a transaction and a snapshot (unlike
> functions/DO)"
>
> All this seems to be a huge change which will definitely not appear any
> time soon.
>


I am willing to bet that DO $$ $$; blocks are neither planned nor
parameterized.  And the planner needs to know what is to be returned.

So anything you add to make DO work well with the planner will probably end
you right back at the same amount of overhead as a temporary function.


>
> On Sun, Aug 14, 2016 at 10:42 AM, Chris Travers 
> wrote:
>
>> If all you want is a temporary function, you *can* create it in the
>> pg_temp namespace though that seems hackish.
>>
>> Maybe a better solution would be to extend CREATE FUNCTION in a way that
>> allows you to CREATE TEMPORARY FUNCTION?
>>
>> ...
>>
>> --
>> Best Wishes,
>> Chris Travers
>>
>> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
>> lock-in.
>> http://www.efficito.com/learn_more
>>
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

[Chris Travers]

On Sun, Aug 14, 2016 at 12:35 PM, Karsten Hilbert 
wrote:

> On Fri, Aug 12, 2016 at 01:32:33PM +0200, Chris Travers wrote:
>
> >>> My preference is stored procedures plus service locators
> >>
> >> Would you care to elaborate a little on the latter (service locators) ?
> >>
> >
> > Sure.  What I prefer to do is to allow for a (cacheable) lookup on the
> > basis of some criteria, either:
> > 1.  Function name or
> > 2.  Function name and first argument type
> >
> > This assumes that whichever discovery criteria you are using leads to
> > uniquely identifying a function.
> >
> > Then from the argument list, I know the names and types of the arguments,
> > and the service locator can map them in.  This means:
> >
> > 1.  You can expose an API which calls arguments by name rather than just
> > position, and
> > 2.  You can add arguments of different types without breaking things as
> > long as it is agreed that unknown arguments are passed in as NULL.
>
> Maybe I am a bit dense. Can you please give an example ?
>


Ok.  Two ways of doing this based on different discovery criteria..  The
first would be:

CREATE FUNCTION person_save(in_id int, in_first_name text, in_last_name
text, in_date_of_birth date)
RETURNS person LANGUAGE ... as $$ ... $$;

Then you have a service locator that says "I have a person object and want
to call person_save."  It then looks up the function argument names and
calls it something like this:

SELECT * FROM  person_save(?, ?, ?, ?)

with parameters
$object->id, $object->first_name, $object->last_name, $object->date_of_birth

The second approach is to tie to the first argument type (think 'self' in
Python).

In this case, we'd have a function defined like this:

CREATE FUNCTION save(person) RETURNS person LANGUAGE ... AS $$ ...$$;

Then we have a different service locator that maps this to the safe
function as:

SELECT * FROM save(?::person);

with a argument that is basically:

serialize_to_record_form($object)

Of course that's just the start. To make this really usable you have to add
some additional functionality but that should be enough to describe the
process.


>
> Thanks,
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

[Karsten Hilbert]

On Fri, Aug 12, 2016 at 01:32:33PM +0200, Chris Travers wrote:

>>> My preference is stored procedures plus service locators
>>
>> Would you care to elaborate a little on the latter (service locators) ?
>>
> 
> Sure.  What I prefer to do is to allow for a (cacheable) lookup on the
> basis of some criteria, either:
> 1.  Function name or
> 2.  Function name and first argument type
> 
> This assumes that whichever discovery criteria you are using leads to
> uniquely identifying a function.
> 
> Then from the argument list, I know the names and types of the arguments,
> and the service locator can map them in.  This means:
> 
> 1.  You can expose an API which calls arguments by name rather than just
> position, and
> 2.  You can add arguments of different types without breaking things as
> long as it is agreed that unknown arguments are passed in as NULL.

Maybe I am a bit dense. Can you please give an example ?

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Xtra Coder]

Thanks, I'm aware about ability to create temp functions, but this is
actually too much overhead - I mean unneeded boilerplate code, but it seems
in current state it is "the least evil" which I have to use.

I think 'what i need' is support for following
- ability to switch session language from 'sql' to 'pl/pgsql'
- in that mode - ability to declare session-scope variables, 'DO' is just
not needed after that
- SELECTs not targeted into a variable - are written to client output
- (C) Merlin Moncure - "Ability to embed collection of statements in the
database under a name and invoke those statements via CALL , which
does not automatically create a transaction and a snapshot (unlike
functions/DO)"

All this seems to be a huge change which will definitely not appear any
time soon.

On Sun, Aug 14, 2016 at 10:42 AM, Chris Travers 
wrote:

> If all you want is a temporary function, you *can* create it in the
> pg_temp namespace though that seems hackish.
>
> Maybe a better solution would be to extend CREATE FUNCTION in a way that
> allows you to CREATE TEMPORARY FUNCTION?
>
> ...
>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Chris Travers]

If all you want is a temporary function, you *can* create it in the pg_temp
namespace though that seems hackish.

Maybe a better solution would be to extend CREATE FUNCTION in a way that
allows you to CREATE TEMPORARY FUNCTION?

On Sun, Aug 14, 2016 at 9:28 AM, Xtra Coder  wrote:

> Thanks for the link. After looking through it i see following major points:
>
> - thread is from 2013 and nothing changed today in 2016
> - quote from that thread (C) Dimitri Fontaine
>"That topic apparently raises each year and rehash the same points"
>(So ... there should be more similar discussions in previous years,
> i.e. topic was first stated more than 3 years ago)
> - most people agree that DO somehow needs to be made 'RETURNING', but
> there is no consensus how this should be made
>
> And out of that I feel there is no luck with that feature in foreseeable
> future :(.
>
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

[Xtra Coder]

Thanks for the link. After looking through it i see following major points:

- thread is from 2013 and nothing changed today in 2016
- quote from that thread (C) Dimitri Fontaine
   "That topic apparently raises each year and rehash the same points"
   (So ... there should be more similar discussions in previous years, i.e.
topic was first stated more than 3 years ago)
- most people agree that DO somehow needs to be made 'RETURNING', but there
is no consensus how this should be made

And out of that I feel there is no luck with that feature in foreseeable
future :(.