[GENERAL] postgresql-9.5.3 compilation on Solaris SPARC

[Venkata Balaji N]

Hi,

"make" command is generating the following error while compiling
postgresql-9.5.3 on Solaris SPARC.

I tried compiling 9.2 and 9.3, works fine. This is only happening on 9.5.

../../src/port/libpgport_srv.a ../../src/common/libpgcommon_srv.a -lnsl
-lrt -lsocket -lm -o postgres

Undefined   first referenced

symbol in file

atomic_cas_64   port/atomics.o

atomic_cas_32   port/atomics.o

ld: fatal: Symbol referencing errors. No output written to postgres

make[2]: *** [postgres] Error 1

make[2]: Leaving directory `/opt/postgresql-9.5.3/src/backend'

make[1]: *** [all-backend-recurse] Error 2

make[1]: Leaving directory `/opt/postgresql-9.5.3/src'

make: *** [all-src-recurse] Error 2


Any help would be appreciated. Do i need to give any particular CFLAGS ?


Below is the *configure *command -


./configure --prefix=/opt/postgres/9.5.3 CC=/opt/SUNWspro/bin/cc
'CFLAGS=-xO3 -xarch=v9 -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff
-xc99=none -xCC' --enable-cassert --without-readline --enable-thread-safety
LDFLAGS='-L/usr/lib/sparcv9 -L/usr/ucblib/sparcv9'


I tried adding an option  CFLAGS_SSE42='-msse4.2'. No luck.

*Operating System*

-bash-3.00$ uname -a
SunOS sunfire2 5.10 Generic sun4u sparc SUNW,Sun-Fire-V210

*configure command output. Please let me know if you need config.log file*

-checking build system type... sparc-sun-solaris2.10
checking host system type... sparc-sun-solaris2.10
checking which template to use... solaris
checking whether to build with 64-bit integer date/time support... yes
checking whether NLS is wanted... no
checking for default port number... 5432
checking for block size... 8kB
checking for segment size... 1GB
checking for WAL block size... 8kB
checking for WAL segment size... 16MB
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... no
checking whether /opt/SUNWspro/bin/cc accepts -g... yes
checking for /opt/SUNWspro/bin/cc option to accept ISO C89... none needed
checking whether the C compiler still works... yes
checking how to run the C preprocessor... /opt/SUNWspro/bin/cc -Xa -E
checking allow thread-safe client libraries... yes
checking whether to build with Tcl... no
checking whether to build Perl modules... no
checking whether to build Python modules... no
checking whether to build with GSSAPI support... no
checking whether to build with PAM support... no
checking whether to build with LDAP support... no
checking whether to build with Bonjour support... no
checking whether to build with OpenSSL support... no
checking whether to build with SELinux support... no
checking for grep that handles long lines and -e... /usr/sfw/bin/ggrep
checking for egrep... /usr/sfw/bin/ggrep -E
checking for non-GNU ld... /usr/ucb/ld
checking if the linker (/usr/ucb/ld) is GNU ld... no
checking for ranlib... ranlib
checking for strip... strip
checking whether it is possible to strip libraries... no
checking for ar... ar
checking for a BSD-compatible install... config/install-sh -c
checking for tar... /usr/bin/tar
checking whether ln -s works... yes
checking for gawk... no
checking for mawk... no
checking for nawk... nawk
checking for a thread-safe mkdir -p... /opt/sfw/bin/gmkdir -p
checking for bison... /usr/sfw/bin/bison
configure: using bison (GNU Bison) 1.875
checking for flex... configure: WARNING:
*** The installed version of Flex, /usr/sfw/bin/flex, is too old to use
with PostgreSQL.
*** Flex version 2.5.31 or later is required, but this is /usr/sfw/bin/flex
version 2.5.4.
no
configure: WARNING:
*** Without Flex you will not be able to build PostgreSQL from Git nor
*** change any of the scanner definition files.  You can obtain Flex from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this because the Flex
*** output is pre-generated.)
checking for perl... /usr/bin/perl
configure: using perl 5.8.4
checking for main in -lm... yes
checking for library containing setproctitle... no
checking for library containing dlopen... none required
checking for library containing socket... -lsocket
checking for library containing shl_load... no
checking for library containing getopt_long... none required
checking for library containing crypt... none required
checking for library containing shm_open... -lrt
checking for library containing shm_unlink... none required
checking for library containing fdatasync... none required
checking for library containing sched_yield... none required
checking for library containing gethostbyname_r... -lnsl
checking for library containing shmget... none required
checking for inflate in -lz... yes
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for 

Re: [GENERAL] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

[Martín Marqués]

El 19/05/16 a las 16:15, Cameron Smith escribió:
> I'd agree:  most likely a file system problem.  Is there any hope that this 
> file could be re-built?
> 
> My current plan is to use bdr_part_by_node_names to remove the failing node 
> and then rebuild it from a fresh backup (and probably on a new server).

I think the most sensible plan is to remove the node from the bdr
cluster with bdr_part_by_node_name(), maybe clean up the bdr_nodes table
(some won't be happy with me suggesting this :)), remove the data
directory on the failed node and rejoin with bdr_init_copy

I'd suggest following the suggestions from Christoph and check that you
have a sane file-system configuration.

Also check if you didn't end up with a damaged disk (run some stress
test on the hardware).

If this is on production (not a toy installation) I would suggest
replacing the disks all together.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] Debugging a backend stuck consuming CPU

[Tom Lane]

"k...@rice.edu"  writes:
> The stack trace just appeared to be what I would expect while a 'DISCARD ALL'
> command was being run:

> #0  0x0073bc7c in MemoryContextSetParent ()
> #1  0x0073bde3 in MemoryContextDelete ()
> #2  0x0054e3a9 in DropAllPreparedStatements ()
> #3  0x005365f3 in DiscardCommand ()

Hmm, what it seems from these traces is that you've got a whole heck of
a lot of prepared statements.

> The backend does have a very large memory footprint (12GB).

Um.

The most likely explanation is that you are hitting O(N^2) behavior as
a consequence of MemoryContextSetParent being O(N) in the number of
sibling contexts of the context to be deleted.  We fixed that for 9.6
(commit 25c539233044c235e97fd7c9dc600fb5f08fe065) but there's no easy
solution in older branches, short of not using so many prepared
statements.  I'm a bit surprised that you could have gotten up to 12GB
worth of prepared statements in an application that sends DISCARD ALL
periodically.

regards, tom lane


-- 
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] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

[Cameron Smith]

I'd agree:  most likely a file system problem.  Is there any hope that this 
file could be re-built?

My current plan is to use bdr_part_by_node_names to remove the failing node and 
then rebuild it from a fresh backup (and probably on a new server).

Thank you for your help!

Cameron Smith



From: Alvaro Herrera 
Sent: May 19, 2016 2:56 PM
To: Cameron Smith
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL with BDR - PANIC:  could not create 
replication identifier checkpoint

CAUTION EXTERNAL EMAIL






Cameron Smith wrote:

> t:2016-05-19 01:14:51.668 UTC d= p=144 a=PANIC:  could not create replication 
> identifier checkpoint "pg_logical/checkpoints/8-F3923F98.ckpt.tmp": Invalid 
> argument

This line corresponds to the following code in BDR's 9.4.4
src/backend/replication/logical/replication_identifier.c:

/*
 * no other backend can perform this at the same time, we're protected by
 * CheckpointLock.
 */
tmpfd = OpenTransientFile(tmppath,
  O_CREAT | O_EXCL | O_WRONLY | PG_BINARY,
  S_IRUSR | S_IWUSR);
if (tmpfd < 0)
ereport(PANIC,
(errcode_for_file_access(),
 errmsg("could not create replication identifier checkpoint 
\"%s\": %m",
tmppath)));

This file does not exist in 9.5, but instead we have
src/backend/replication/logical/origin.c which has identical code.

OpenTransientFile calls BasicOpenFile, which in turn calls open() and
propagates the errno.  My manpage doesn't list any possible reasons for
open() to return EINVAL, so I'm at a loss about what is happening here.
Maybe this is a filesystem problem?

--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
DO NOT open attachments or click on links from unknown senders or unexpected 
emails





This e-mail and any attachments are intended only for use by the addressee(s) 
named herein and may contain confidential information. If you are not the 
intended recipient of this e-mail, you are hereby notified any dissemination, 
distribution or copying of this email and any attachments is strictly 
prohibited. If you receive this email in error, please immediately notify the 
sender by return email and permanently delete the original, any copy and any 
printout thereof. The integrity and security of e-mail cannot be guaranteed.


-- 
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] Debugging a backend stuck consuming CPU

[k...@rice.edu]

On Thu, May 19, 2016 at 09:58:45AM -0400, Tom Lane wrote:
> "k...@rice.edu"  writes:
> > I am investigating a problem with a backend that appears to be stuck
> > and spinning while performing a "DISCARD ALL" command. The system is
> > running an older release 9.2.2.
> 
> You do realize that the current release in that series is 9.2.17.
> 
> > Are there any bugs that could be causing this behavior?
> 
> Known bugs are summarized here:
> http://www.postgresql.org/docs/9.2/static/release.html
> 
> > How can I tell what the process is actually doing?
> 
> Getting a stack trace with gdb might be informative:
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
> 
>   regards, tom lane
> 

Hi,

The stack trace just appeared to be what I would expect while a 'DISCARD ALL'
command was being run:

Continuing.

Program received signal SIGINT, Interrupt.
0x0073bc7c in MemoryContextSetParent ()
#0  0x0073bc7c in MemoryContextSetParent ()
#1  0x0073bde3 in MemoryContextDelete ()
#2  0x0054e3a9 in DropAllPreparedStatements ()
#3  0x005365f3 in DiscardCommand ()
#4  0x006582c7 in ?? ()
#5  0x006592bd in ?? ()
#6  0x00659a42 in PortalRun ()
#7  0x0065603d in ?? ()
#8  0x00656ed0 in PostgresMain ()
#9  0x00613b91 in ?? ()
#10 0x006167fc in PostmasterMain ()
#11 0x005b5290 in main ()
Continuing.

Program received signal SIGINT, Interrupt.
0x0073bc7c in MemoryContextSetParent ()
#0  0x0073bc7c in MemoryContextSetParent ()
#1  0x0073bde3 in MemoryContextDelete ()
#2  0x0054e3a9 in DropAllPreparedStatements ()
#3  0x005365f3 in DiscardCommand ()
#4  0x006582c7 in ?? ()
#5  0x006592bd in ?? ()
#6  0x00659a42 in PortalRun ()
#7  0x0065603d in ?? ()
#8  0x00656ed0 in PostgresMain ()
#9  0x00613b91 in ?? ()
#10 0x006167fc in PostmasterMain ()
#11 0x005b5290 in main ()
Continuing.

Program received signal SIGINT, Interrupt.
0x0073bc7c in MemoryContextSetParent ()
#0  0x0073bc7c in MemoryContextSetParent ()
#1  0x0073bde3 in MemoryContextDelete ()
#2  0x0070e7df in DropCachedPlan ()
#3  0x0054e3a9 in DropAllPreparedStatements ()
#4  0x005365f3 in DiscardCommand ()
#5  0x006582c7 in ?? ()
#6  0x006592bd in ?? ()
#7  0x00659a42 in PortalRun ()
#8  0x0065603d in ?? ()
#9  0x00656ed0 in PostgresMain ()
#10 0x00613b91 in ?? ()
#11 0x006167fc in PostmasterMain ()
#12 0x005b5290 in main ()
Continuing.

Program received signal SIGINT, Interrupt.
0x0073bc7c in MemoryContextSetParent ()
#0  0x0073bc7c in MemoryContextSetParent ()
#1  0x0073bde3 in MemoryContextDelete ()
#2  0x0054e3a9 in DropAllPreparedStatements ()
#3  0x005365f3 in DiscardCommand ()
#4  0x006582c7 in ?? ()
#5  0x006592bd in ?? ()
#6  0x00659a42 in PortalRun ()
#7  0x0065603d in ?? ()
#8  0x00656ed0 in PostgresMain ()
#9  0x00613b91 in ?? ()
#10 0x006167fc in PostmasterMain ()
#11 0x005b5290 in main ()
Continuing.

Program received signal SIGINT, Interrupt.
0x0070e7ff in DropCachedPlan ()
#0  0x0070e7ff in DropCachedPlan ()
#1  0x0054e3a9 in DropAllPreparedStatements ()
#2  0x005365f3 in DiscardCommand ()
#3  0x006582c7 in ?? ()
#4  0x006592bd in ?? ()
#5  0x00659a42 in PortalRun ()
#6  0x0065603d in ?? ()
#7  0x00656ed0 in PostgresMain ()
#8  0x00613b91 in ?? ()
#9  0x006167fc in PostmasterMain ()
#10 0x005b5290 in main ()
Detaching from program: /usr/pgsql-9.2/bin/postgres, process 38604
Undefined command: "exit".  Try "help".
Continuing.

Program received signal SIGINT, Interrupt.
0x0070e7ff in DropCachedPlan ()
#0  0x0070e7ff in DropCachedPlan ()
#1  0x0054e3a9 in DropAllPreparedStatements ()
#2  0x005365f3 in DiscardCommand ()
#3  0x006582c7 in ?? ()
#4  0x006592bd in ?? ()
#5  0x00659a42 in PortalRun ()
#6  0x0065603d in ?? ()
#7  0x00656ed0 in PostgresMain ()
#8  0x00613b91 in ?? ()
#9  0x006167fc in PostmasterMain ()
#10 0x005b5290 in main ()
Continuing.

Program received signal SIGINT, Interrupt.
0x0070e7ff in DropCachedPlan ()
#0  0x0070e7ff in DropCachedPlan ()
#1  0x0054e3a9 in DropAllPreparedStatements ()
#2  0x005365f3 in DiscardCommand ()
#3  0x006582c7 in ?? ()
#4  0x006592bd in ?? ()
#5  0x00659a42 in PortalRun ()
#6  0x0065603d in ?? ()
#7  0x00656ed0 in PostgresMain ()
#8  0x00613b91 in ?? ()
#9  0x006167fc in PostmasterMain ()
#10 0x005b5290 in main ()
Continuing.

Program received signal 

Re: [GENERAL] PQcancel may hang in the recv call

[Tom Lane]

"David G. Johnston"  writes:
> On Thu, May 19, 2016 at 3:32 PM, Tom Lane  wrote:
>> I do not recall anyone ever reporting something similar --- and that code
>> has been like that for a long time.

> ​I'd take Tom's word over mine :)​

Well, my memory is often faulty ;-).  But I did trawl the PG archives
for a bit, and didn't find anything quite like this.  There are complaints
about PQcancel not working if the network is down, but no reports that it
hangs, as far as I can find.

regards, tom lane


-- 
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] PQcancel may hang in the recv call

[David G. Johnston]

On Thu, May 19, 2016 at 3:32 PM, Tom Lane  wrote:

> Peter Juhasz  writes:
>
> > Is this known?
>
> I do not recall anyone ever reporting something similar --- and that code
> has been like that for a long time.
>
>
​I'd take Tom's word over mine :)​

​David J.​

Re: [GENERAL] PQcancel may hang in the recv call

[Tom Lane]

Peter Juhasz  writes:
> We've found a situation where canceling a query may cause the client to
> hang, possibly indefinitely. This can happen if the network connection
> fails in a specific way.
> ...
> However, if the network fails in a way that the connection appears to
> have been established but subsequent packages are dropped silently,
> this recv() call will block.

Hmm.  I would expect the recv to eventually fail based on TCP timeouts,
but I agree that that would be much longer than you'd typically wish
to wait.

> Is this known?

I do not recall anyone ever reporting something similar --- and that code
has been like that for a long time.

> Is this a bug?

I wouldn't call it that exactly.  There might be an opportunity for
improvement here, but it's not very clear what.  Just introducing a
timeout would likely create more problems than it fixes, considering the
evident rarity of the problem.  The race condition hazard that the recv()
is trying to prevent is definitely real: we used to not have that, and
we got bug reports, cf
http://www.postgresql.org/message-id/flat/20030915070801.gd23...@opencloud.com

regards, tom lane


-- 
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] PQcancel may hang in the recv call

[David G. Johnston]

On Thu, May 19, 2016 at 10:37 AM, Peter Juhasz 
wrote:

> Hi all,
>
> this is somewhat involved so please bear with me.
>
> We've found a situation where canceling a query may cause the client to
> hang, possibly indefinitely. This can happen if the network connection
> fails in a specific way.
>
> The reason for this lies in the way the PQcancel function (which
> eventually gets called from the higher level interface's cancel
> function) is implemented. It works by opening a second connection to
> the postmaster (on the same host/port as the existing connection),
> send()-ing a cancellation message via the newly opened connection, then
> calling recv() to receive an indication that the message was processed.
>
> However, if the network fails in a way that the connection appears to
> have been established but subsequent packages are dropped silently,
> this recv() call will block.
>
> My questions:
>
> Is this known?
> Is this a bug?
> What can be done to fix or work around it, apart from applying a
> timeout wrapper the cancel operation as well?
>
>
​It does sound familiar.  Providing the version number(s) on which you
encountered this behavior would be helpful.  Or HEAD if you have or are
testing against current code.

David J.

Re: [GENERAL] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

[Christoph Moench-Tegeder]

## Cameron Smith (csm...@stereodllc.com):

> t:2016-05-19 01:14:51.668 UTC d= p=144 a=PANIC:  could not create replication 
> identifier checkpoint "pg_logical/checkpoints/8-F3923F98.ckpt.tmp": Invalid 
> argument
> t:2016-05-19 01:14:51.671 UTC d= p=9729 a=WARNING:  could not create 
> relation-cache initialization file "global/pg_internal.init.9729": Invalid 
> argument

This and the other "Invalid Argument" errors give the impression that
your "power issue" has wrecked your filesystem - as Alvaro already
pointed out, there's no sane way that an open() returns EINVAL in
PostgreSQL on any "recommended" filesystem.
This shouldn't happen with any decently modern filesystem and "safe"
hardware. Did you tweak anything on your filesystem? Did you check
your storage stack against https://wiki.postgresql.org/wiki/Reliable_Writes?

Regards,
Christoph

-- 
Spare Space


-- 
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] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

[Alvaro Herrera]

Cameron Smith wrote:

> t:2016-05-19 01:14:51.668 UTC d= p=144 a=PANIC:  could not create replication 
> identifier checkpoint "pg_logical/checkpoints/8-F3923F98.ckpt.tmp": Invalid 
> argument

This line corresponds to the following code in BDR's 9.4.4
src/backend/replication/logical/replication_identifier.c:

/*
 * no other backend can perform this at the same time, we're protected by
 * CheckpointLock.
 */
tmpfd = OpenTransientFile(tmppath,
  O_CREAT | O_EXCL | O_WRONLY | PG_BINARY,
  S_IRUSR | S_IWUSR);
if (tmpfd < 0)
ereport(PANIC,
(errcode_for_file_access(),
 errmsg("could not create replication identifier checkpoint 
\"%s\": %m",
tmppath)));

This file does not exist in 9.5, but instead we have
src/backend/replication/logical/origin.c which has identical code.

OpenTransientFile calls BasicOpenFile, which in turn calls open() and
propagates the errno.  My manpage doesn't list any possible reasons for
open() to return EINVAL, so I'm at a loss about what is happening here.
Maybe this is a filesystem problem?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] PostgreSQL with BDR - PANIC: could not create replication identifier checkpoint

[Cameron Smith]

Hi PostgreSQL community:


We have a three node postgresql BDR set up.  One of our nodes went down due to 
a power issue.  After bringing the server back online the OS reported the need 
to repair some files.  Once this completed and we restarted the postgresql 
service, we noticed that it was crashing very quickly.  Checking the logs 
revealed some panics (please see below).  The other two nodes appear to up and 
running and replicating with each other but our WAL backlog is slowly growing.


A description of what you are trying to achieve and what results you expect:
Repair a damaged node and get it replicating to/from the other nodes in our 
cluster.

PostgreSQL version number you are running:
PostgreSQL 9.4.4 on x86 64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 
20120313 (Red Hat 4.4.7-11), 64-bit

How you installed PostgreSQL:
Downloaded postgresql with BDR RPMs from 2nd Quandrant.

Changes made to the settings in the postgresql.conf file:
"application_name";"pgAdmin III - Query Tool";"client"
"bdr.log_conflicts_to_table";"on";"configuration file"
"bytea_output";"escape";"session"
"client_encoding";"SQL_ASCII";"session"
"client_min_messages";"notice";"session"
"DateStyle";"ISO, MDY";"session"
"default_sequenceam";"bdr";"configuration file"
"default_text_search_config";"pg_catalog.english";"configuration file"
"dynamic_shared_memory_type";"posix";"configuration file"
"lc_messages";"en_US.UTF-8";"configuration file"
"lc_monetary";"en_US.UTF-8";"configuration file"
"lc_numeric";"en_US.UTF-8";"configuration file"
"lc_time";"en_US.UTF-8";"configuration file"
"listen_addresses";"*";"configuration file"
"log_destination";"stderr";"configuration file"
"log_directory";"/var/log/postgresql";"configuration file"
"log_error_verbosity";"default";"configuration file"
"log_filename";"postgresql-%Y-%m-%d_%H%M%S.log";"configuration file"
"log_line_prefix";"t:%m d=%d p=%p a=%a%q ";"configuration file"
"log_min_messages";"info";"configuration file"
"log_rotation_age";"1d";"configuration file"
"log_rotation_size";"0";"configuration file"
"log_timezone";"UTC";"configuration file"
"log_truncate_on_rotation";"on";"configuration file"
"logging_collector";"on";"configuration file"
"max_connections";"100";"configuration file"
"max_replication_slots";"10";"configuration file"
"max_stack_depth";"2MB";"environment variable"
"max_wal_senders";"10";"configuration file"
"max_worker_processes";"10";"configuration file"
"port";"54330";"command line"
"shared_buffers";"128MB";"configuration file"
"shared_preload_libraries";"bdr";"configuration file"
"TimeZone";"UTC";"configuration file"
"track_commit_timestamp";"on";"configuration file"
"wal_level";"logical";"configuration file"

Operating system and version:
CentOS release 6.6
Linux  2.6.32-504.el6.x86_64 #1 SMP Wed Oct 15 04:27:16 
UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

For questions about any kind of error:

What program you're using to connect to PostgreSQL:
Django 1.7.0

Is there anything relevant or unusual in the PostgreSQL server logs?:
Hit a panic when attempting to create a replication identifier checkpoint.  
Subsequent restarts of postgresql failed with a panic complaining that it could 
not open a replication slot file.

...
t:2016-05-19 01:14:51.668 UTC d= p=144 a=PANIC:  could not create replication 
identifier checkpoint "pg_logical/checkpoints/8-F3923F98.ckpt.tmp": Invalid 
argument
t:2016-05-19 01:14:51.671 UTC d= p=9729 a=WARNING:  could not create 
relation-cache initialization file "global/pg_internal.init.9729": Invalid 
argument
t:2016-05-19 01:14:51.671 UTC d= p=9729 a=DETAIL:  Continuing anyway, but 
there's something wrong.
t:2016-05-19 01:14:51.674 UTC d= p=133 a=LOG:  checkpointer process (PID 144) 
was terminated by signal 6: Aborted
t:2016-05-19 01:14:51.674 UTC d= p=133 a=LOG:  terminating any other active 
server processes
t:2016-05-19 01:14:51.675 UTC d= p=147 a=WARNING:  terminating connection 
because of crash of another server process
t:2016-05-19 01:14:51.675 UTC d= p=147 a=DETAIL:  The postmaster has commanded 
this server process to roll back the current transaction and exit, because 
another server process exited abnormally and possibly corrupted shared memory.
t:2016-05-19 01:14:51.675 UTC d= p=147 a=HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
t:2016-05-19 01:14:51.675 UTC d= p=148 a=LOG:  could not open temporary 
statistics file "pg_stat/global.tmp": Invalid argument
t:2016-05-19 01:14:51.694 UTC d= p=9729 a=WARNING:  terminating connection 
because of crash of another server process
t:2016-05-19 01:14:51.694 UTC d= p=9729 a=DETAIL:  The postmaster has commanded 
this server process to roll back the current transaction and exit, because 
another server process exited abnormally and possibly corrupted shared memory.
t:2016-05-19 01:14:51.694 UTC d= p=9729 a=HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
t:2016-05-19 01:14:51.786 UTC d= p=9730 

Re: [GENERAL] Londiste 3 pgq events_1_1 table huge

[Saiful Muhajir]

This has happened to us where we have dead or unmanaged consumer. Turns out
londiste is keeping the event even if the consumer is unreachable. This is
to ensure that the consumer gets what it should.

To clean this up, delete the unused/dead consumer, with qadmin or manually
if necessary. The table won't be deleted immediately though. We have to
restart pgqd and workers and wait for two days.

~
Saiful Muhajir
On 19 May 2016 20:01, "Rene ."  wrote:

> Queue - Event are stored in queue tables i.e queues. Several producers can
> write
> into same queue and several consumers can read from the queue. Events are
> kept
> in queue until all the consumers have seen them.
>
> Maybe you have some inactive consumers holding a event tables.
>
> qadmin -h  -p 5432 -U postgres -d  -Q
> 
>
> Use 'show help;' to see available commands.
> copy output of show consumer command
>
> show consumer;
>
> Rene
>
> 
> From: pgsql-general-ow...@postgresql.org <
> pgsql-general-ow...@postgresql.org> on behalf of Leonardo M. Ramé <
> l.r...@griensu.com>
> Sent: Thursday, May 19, 2016 2:43 PM
> To: PostgreSql-general
> Subject: Re: [GENERAL] Londiste 3 pgq events_1_1 table huge
>
> El 18/05/16 a las 19:03, Rene . escribió:
> > Hi, Check for long running Idle in transaction sessions. Idle in
> transaction sessions may holding events table from cleaning itself up.
> > If there is more then days long running idle in transaction sessions,
> kill them, event table should be cleaned automatically after that.
> >
> > "select pid,state, query_start from pg_stat_activity where state='idle
> in transaction';" for checking sessions.
> >
> > Rene
>
> Thanks Rene, I found only one "idle in transaction" and it dates from
> just a couple of minutes ago, so, the reason of huge event table must be
> something else.
>
> By looking at the event_1_1 table I found records from march, but
> londiste3 status shows everything is already in sync:
>
> nodo_master (root)
>|   Tables: 146/0/0
>|   Lag: 0s, Tick: 1112197
>+--: node_esclavo (leaf)
>Tables: 146/0/0
>Lag: 0s, Tick: 1112197
>
> So, what if I manually delete old events?.
>
>
> Regards,
> --
> Leonardo M. Ramé
> Medical IT - Griensu S.A.
> Av. Colón 636 - Piso 8 Of. A
> X5000EPT -- Córdoba
> Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
> Cel.: +54 9 (011) 40871877
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] PQcancel may hang in the recv call

[Peter Juhasz]

Hi all,

this is somewhat involved so please bear with me.

We've found a situation where canceling a query may cause the client to
hang, possibly indefinitely. This can happen if the network connection
fails in a specific way.

The reason for this lies in the way the PQcancel function (which
eventually gets called from the higher level interface's cancel
function) is implemented. It works by opening a second connection to
the postmaster (on the same host/port as the existing connection),
send()-ing a cancellation message via the newly opened connection, then
calling recv() to receive an indication that the message was processed.

However, if the network fails in a way that the connection appears to
have been established but subsequent packages are dropped silently,
this recv() call will block.

My questions:

Is this known?
Is this a bug?
What can be done to fix or work around it, apart from applying a
timeout wrapper the cancel operation as well?


The attached example program attempts to demonstrate the effect.
It simulates network outage by routing data through a local TCP proxy
that stops forwarding packets at a given point. It's written in Perl
for convenience, but the problem is not in the Perl part: running it
with strace will clearly show that it hangs at the recv() call in fe-
connect.c:internal_cancel().

The program assumes that you have a postgresql server listening on port
5432 on localhost, and you can log in to a database called 'postgres'
with user 'postgres' (but edit either your local postgresql settings or
the connection string in the program if it doesn't work).

Ran without any command line options, the program simulates a long-
running query with pg_sleep(), and prints the result, which should be
'ok'. This should take about 3 seconds.

Ran with the -c option, it cancels the query after one second.

Ran with the -d option, it instructs the proxy to drop packets, so the
main program will never receive the result and timeouts after 6
seconds.

With both the -c and -d options it drops packets, then attempts to
cancel, and this is where it gets interesting: it hangs for 60
seconds. 

With options -c -d -a 1, it doesn't allow the second connection to go
through, in which case it hangs (seemingly) forever.

The -v option can be added to print debug messages.

(For those unfamiliar with Perl, the program works by forking twice:
after the first fork the child process starts the proxy, then after the
second fork the parent process proceeds with the database connection,
while the second child sends signals to the proxy or the main process,
depending on the command line settings.)


Best regards,
Péter Juhász

pg_cancel_bug.pl
Description: Perl program

-- 
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] Debugging a backend stuck consuming CPU

[Merlin Moncure]

On Thu, May 19, 2016 at 8:46 AM, k...@rice.edu  wrote:
> Hi PostgreSQL community,
>
> I am investigating a problem with a backend that appears to be stuck
> and spinning while performing a "DISCARD ALL" command. The system is
> running an older release 9.2.2. Are there any bugs that could be
> causing this behavior? How can I tell what the process is actually
> doing? It does not respond to either pg_cancel_backend() or to
> pg_terminate_backend() so it is acting like it is not receiving the
> signals. Any ideas on how to debug this would be appriciated.

1. Grab a stack trace and a 'perf top' for forensic analysis in case
it happens again
2. Install latest 9.2 binaries
3. Restart database

There's not much value in analyzing such an out of date database;
there's a very high probability your bug has already been fixed.

merlin


-- 
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] PG wire protocol question

[Merlin Moncure]

On Wed, May 18, 2016 at 5:05 AM, Boszormenyi Zoltan  wrote:
> 2016-05-17 15:29 keltezéssel, Albe Laurenz írta:
>>
>> Boszormenyi Zoltan wrote:
>>>
>>> it was a long time I have read this list or written to it.
>>>
>>> Now, I have a question. This blog post was written about 3 years ago:
>>> https://aphyr.com/posts/282-jepsen-postgres
>>>
>>> Basically, it talks about the client AND the server as a system
>>> and if the network is cut between sending COMMIT and
>>> receiving the answer for it, the client has no way to know
>>> whether the transaction was actually committed.
>>>
>>> The client connection may just timeout and a reconnect would
>>> give it a new connection but it cannot pick up its old connection
>>> where it left. So it cannot really know whether the old transaction
>>> was committed or not, possibly without doing expensive queries first.
>>>
>>> Has anything changed on that front?
>>
>> That blog post seems ill-informed - that has nothing to do with
>> two-phase commit.
>
> Not quite. That would mean constantly sending an ack that the other
> received the last ack, which would be silly.
>
> If the network connection is cut, the client should be able to
> reconnect to the old backend and query the last state and continue
> where it left, maybe confirming via some key or UUID that it was
> indeed the client that connected previously.

I agree. It's the server's job to make sure itself is consistent.  If
the client is suspicious it may have lost the ack for whatever reason,
it needs to verify against the database that the transaction
succeeded.  This is an application problem, not a protocol problem.

merlin


-- 
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] Debugging a backend stuck consuming CPU

[k...@rice.edu]

On Thu, May 19, 2016 at 09:58:45AM -0400, Tom Lane wrote:
> "k...@rice.edu"  writes:
> > I am investigating a problem with a backend that appears to be stuck
> > and spinning while performing a "DISCARD ALL" command. The system is
> > running an older release 9.2.2.
> 
> You do realize that the current release in that series is 9.2.17.
> 
> > Are there any bugs that could be causing this behavior?
> 
> Known bugs are summarized here:
> http://www.postgresql.org/docs/9.2/static/release.html
> 
> > How can I tell what the process is actually doing?
> 
> Getting a stack trace with gdb might be informative:
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
> 
>   regards, tom lane
> 

Hi,

We do have an upgrade scheduled. I will get a stack trace and see what
information that provides.

Regards,
Ken


-- 
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] Debugging a backend stuck consuming CPU

[Adrian Klaver]

On 05/19/2016 06:46 AM, k...@rice.edu wrote:

Hi PostgreSQL community,

I am investigating a problem with a backend that appears to be stuck
and spinning while performing a "DISCARD ALL" command. The system is
running an older release 9.2.2. Are there any bugs that could be
causing this behavior? How can I tell what the process is actually
doing? It does not respond to either pg_cancel_backend() or to
pg_terminate_backend() so it is acting like it is not receiving the
signals. Any ideas on how to debug this would be appriciated.


What does the Postgres log show around the time of the command and/or 
currently?


What OS and does it's system log show anything?

Can you query pg_stat_activity?

http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW



Regards,
Ken





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


--
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] Debugging a backend stuck consuming CPU

[Tom Lane]

"k...@rice.edu"  writes:
> I am investigating a problem with a backend that appears to be stuck
> and spinning while performing a "DISCARD ALL" command. The system is
> running an older release 9.2.2.

You do realize that the current release in that series is 9.2.17.

> Are there any bugs that could be causing this behavior?

Known bugs are summarized here:
http://www.postgresql.org/docs/9.2/static/release.html

> How can I tell what the process is actually doing?

Getting a stack trace with gdb might be informative:
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane


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

[GENERAL] Debugging a backend stuck consuming CPU

[k...@rice.edu]

Hi PostgreSQL community,

I am investigating a problem with a backend that appears to be stuck
and spinning while performing a "DISCARD ALL" command. The system is
running an older release 9.2.2. Are there any bugs that could be
causing this behavior? How can I tell what the process is actually
doing? It does not respond to either pg_cancel_backend() or to
pg_terminate_backend() so it is acting like it is not receiving the
signals. Any ideas on how to debug this would be appriciated.

Regards,
Ken


-- 
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] first_value/last_value

[David G. Johnston]

On Thu, May 19, 2016 at 7:10 AM, Alexey Bashtanov  wrote:

> As for the original question unfortunately the way from an extension to
> postgres core is not too easy
> and normally requires an extension to become popular and to be included in
> postgres distribution as a contrib module first.
>

​Do you have a specific example of a patch being written for this feature
and not being accepted into core simply due to lack of popularity?
Generalizing here doesn't do anyone any favors - every patch and feature is
unique.

​I'll agree that somewhat marginal features are often passed on, especially
if there is any non-trivial bike-shedding, but I do suspect that if someone
took the time to write a patch for this and usher it through the commit
process it would stand a good chance of being accepted.  I don't recall a
request for this feature recently (last couple of years) let alone someone
putting forth an actual patch.  That the core committers have not chosen to
work on it is not in itself an indication of their opinion on whether the
feature is worth adding.

There are no coding standards for extensions - there are for core.  One
cannot assume that a functioning extension can simply be dropped in.

David J.

Re: [GENERAL] Use of array_agg and array string on inner query

[shankha]

I got the query:

SELECT c1, c2,
ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as
t2 ON t3.c3 = t2.c2)), ',')
FROM s.t1 t1
GROUP BY c1;
DROP SCHEMA s CASCADE;

Thanks for all the help.


Thanks
Shankha Banerjee


On Wed, May 18, 2016 at 2:40 PM, David G. Johnston
 wrote:
> On Wed, May 18, 2016 at 2:30 PM, shankha  wrote:
>>
>> I cannot move the array_agg to around the column name. It has to work
>> as a inner query
>> .
>
>
> The following form is used to make an array from a subquery:
>
> SELECT ARRAY(SELECT i FROM ( VALUES (1), (2), (3) ) vals (i) );
>
> http://www.postgresql.org/docs/9.5/static/sql-expressions.html
>
> 4.2.12; last example
>
> Not the most obvious place...
>
> David J.
>
>


-- 
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] Londiste 3 pgq events_1_1 table huge

[Rene .]

Queue - Event are stored in queue tables i.e queues. Several producers can write
into same queue and several consumers can read from the queue. Events are kept
in queue until all the consumers have seen them.

Maybe you have some inactive consumers holding a event tables.

qadmin -h  -p 5432 -U postgres -d  -Q 

Use 'show help;' to see available commands.
copy output of show consumer command

show consumer;

Rene


From: pgsql-general-ow...@postgresql.org  
on behalf of Leonardo M. Ramé 
Sent: Thursday, May 19, 2016 2:43 PM
To: PostgreSql-general
Subject: Re: [GENERAL] Londiste 3 pgq events_1_1 table huge

El 18/05/16 a las 19:03, Rene . escribió:
> Hi, Check for long running Idle in transaction sessions. Idle in transaction 
> sessions may holding events table from cleaning itself up.
> If there is more then days long running idle in transaction sessions, kill 
> them, event table should be cleaned automatically after that.
>
> "select pid,state, query_start from pg_stat_activity where state='idle in 
> transaction';" for checking sessions.
>
> Rene

Thanks Rene, I found only one "idle in transaction" and it dates from
just a couple of minutes ago, so, the reason of huge event table must be
something else.

By looking at the event_1_1 table I found records from march, but
londiste3 status shows everything is already in sync:

nodo_master (root)
   |   Tables: 146/0/0
   |   Lag: 0s, Tick: 1112197
   +--: node_esclavo (leaf)
   Tables: 146/0/0
   Lag: 0s, Tick: 1112197

So, what if I manually delete old events?.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


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

-- 
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] Londiste 3 pgq events_1_1 table huge

[Leonardo M . Ramé]

El 18/05/16 a las 19:03, Rene . escribió:

Hi, Check for long running Idle in transaction sessions. Idle in transaction 
sessions may holding events table from cleaning itself up.
If there is more then days long running idle in transaction sessions, kill 
them, event table should be cleaned automatically after that.

"select pid,state, query_start from pg_stat_activity where state='idle in 
transaction';" for checking sessions.

Rene


Thanks Rene, I found only one "idle in transaction" and it dates from 
just a couple of minutes ago, so, the reason of huge event table must be 
something else.


By looking at the event_1_1 table I found records from march, but 
londiste3 status shows everything is already in sync:


nodo_master (root)
  |   Tables: 146/0/0
  |   Lag: 0s, Tick: 1112197
  +--: node_esclavo (leaf)
  Tables: 146/0/0
  Lag: 0s, Tick: 1112197

So, what if I manually delete old events?.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
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] first_value/last_value

[Alexey Bashtanov]

Hello Tom,

I agree such functions are very useful, as they allow you to use 
ordinary aggregation functions such as sum/max/avg
along with first/last ones (traditionally served by DISTINCT ON or 
LIMIT) in the same group-by node

which improves performance and readability.

The first/last extension you mentioned 
http://pgxn.org/dist/first_last_agg/ has a slight disadvantage:
it relies on postgres providing it sorted data if user asks for defined 
ordering.
This makes first/last functions not supporting hashAgg, only groupAgg 
which usually requires explicit sort.


If you find first_last_agg performance poor take a look at argm 
extension http://pgxn.org/dist/argm/ .
It provides similar functionality but works faster as it does not sort 
or make postgres core sort,

but only chooses the first row within each group.

As for the original question unfortunately the way from an extension to 
postgres core is not too easy
and normally requires an extension to become popular and to be included 
in postgres distribution as a contrib module first.


Regards,
  Alexey Bashtanov

On 19/05/16 04:04, Tom Smith wrote:
It would really save all the troubles for many people if postgresql 
has a built-in first/last function  along with sum/avg.
There is already a C extension and a wiki sample  and implemented for 
window function.
I am curious why these two functions were not added along their window 
implementation counter part,

for completness and consistency


On Wed, May 18, 2016 at 10:42 PM, Melvin Davidson 
> wrote:




On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback
> wrote:

Here is an example that works in a single query.  Since you
have two different orders you want the data back in, you need
to use subqueries to get the proper data back, but it works,
and is very fast.

CREATE TEMPORARY TABLE foo AS
SELECT generate_series as bar
FROM generate_series(1, 100);

CREATE INDEX idx_foo_bar ON foo (bar);


SELECT *
FROM (
SELECT bar
FROM foo
ORDER BY bar asc
LIMIT 1
) x
UNION ALL
SELECT *
FROM (
SELECT bar
FROM foo
ORDER BY bar desc
LIMIT 1
) y;

DROP TABLE foo;


Seems to me SELECT min(),  max() FROM deja.vu ;
would also work.


-- 
*Melvin Davidson*

I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] first_value/last_value

[Matija Lesar]

On 19 May 2016 at 05:04, Tom Smith  wrote:

> It would really save all the troubles for many people if postgresql has a
> built-in first/last function  along with sum/avg.
> There is already a C extension and a wiki sample  and  implemented for
> window function.
> I am curious why these two functions were not added along  their window
> implementation counter part,
> for completness and consistency
>
>
> On Wed, May 18, 2016 at 10:42 PM, Melvin Davidson 
> wrote:
>
>>
>>
>> On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback <
>> adambrusselb...@gmail.com> wrote:
>>
>>> Here is an example that works in a single query.  Since you have two
>>> different orders you want the data back in, you need to use subqueries to
>>> get the proper data back, but it works, and is very fast.
>>>
>>> CREATE TEMPORARY TABLE foo AS
>>> SELECT generate_series as bar
>>> FROM generate_series(1, 100);
>>>
>>> CREATE INDEX idx_foo_bar ON foo (bar);
>>>
>>>
>>> SELECT *
>>> FROM (
>>> SELECT bar
>>> FROM foo
>>> ORDER BY bar asc
>>> LIMIT 1
>>> ) x
>>> UNION ALL
>>> SELECT *
>>> FROM (
>>> SELECT bar
>>> FROM foo
>>> ORDER BY bar desc
>>> LIMIT 1
>>> ) y;
>>>
>>> DROP TABLE foo;
>>>
>>
>> Seems to me SELECT min(),  max() FROM deja.vu ; would
>> also work.
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
You can always create your aggregate function for this.

Here is example for getting non null first and last value:

CREATE OR REPLACE FUNCTION firstval_sfunc(anyelement, anyelement)
returns anyelement
language sql
as $BODY$
SELECT CASE WHEN $1 is NULL THEN $2 ELSE $1 END;
$BODY$;

CREATE AGGREGATE myfirstval(anyelement) (
SFUNC = firstval_sfunc,
STYPE = anyelement
);

CREATE OR REPLACE FUNCTION lastval_sfunc(anyelement, anyelement)
returns anyelement
language sql
as $BODY$
SELECT CASE WHEN $2 is NULL THEN $1 ELSE $2 END;
$BODY$;

CREATE AGGREGATE mylastval(anyelement) (
SFUNC = lastval_sfunc,
STYPE = anyelement
);


Outputs:

select myfirstval(b), mylastval(b) from
unnest(array[3,2,null,12,-1]::int[]) b;
 myfirstval | mylastval
+---
  3 |-1

 select myfirstval(b order by b), mylastval(b order by b) from
unnest(array[3,2,null,12,-1]::int[]) b;
 myfirstval | mylastval
+---
 -1 |12

select myfirstval(b), mylastval(b)  from generate_series(10,2) as b;
 myfirstval | mylastval
+---
 10 | 2

select myfirstval(b), mylastval(b) from
unnest(array['c','b','t','x']::text[]) b;
 myfirstval | mylastval
+---
 c  | x

Bye,
Matija Lesar

Re: [GENERAL] How to view creation date and time of a relation

[Achilleas Mantzios]

On 19/05/2016 10:57, Sameer Kumar wrote:



On Thu, May 19, 2016 at 3:29 PM John R Pierce > wrote:

On 5/19/2016 12:18 AM, Shrikant Bhende wrote:


Our application executes come scripts with the code consist of  DDL which 
creates lot of objects in the database in various schemas,also there are lot of 
connections firing the same code. I am
able to locate the IP from where the script is initiated (which is causing 
more load on the database ), but I would like to know if I can pinpoint the 
relations which are created on a specific
 date and time or else I can do something through which I get creation date 
and time of the objects.


you would need to have postgres configured to log DDL, and set the log 
prefix to include timestamping, then you could scan those logs to get that 
information.its not otherwise stored in the
database.


Logging is the best way of capturing these events.

You can probably find out the relfilenode from pg_class for a given relation name (relname) and then go to the datadirectory (or tablespace directory) --> db directory (mapped to oid of pg_database) 
--> filename. Check the date time of the file when it was created.



Is this creation timestamp info exposed in Linux (ext4,xfs,etc?)? Last time I 
checked this info was available in FreeBSD out of the box.

Though I don't think this infra has been built for this very purpose.

-- 
john r pierce, recycling bits in santa cruz


--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] How to view creation date and time of a relation

[Sameer Kumar]

On Thu, May 19, 2016 at 3:29 PM John R Pierce  wrote:

> On 5/19/2016 12:18 AM, Shrikant Bhende wrote:
>
>
> Our application executes come scripts with the code consist of  DDL which
> creates lot of objects in the database in various schemas,also there are
> lot of connections firing the same code. I am able to locate the IP from
> where the script is initiated (which is causing more load on the database
> ), but I would like to know if I can pinpoint the relations which are
> created on a specific  date and time or else I can do something through
> which I get creation date and time of the objects.
>
>
> you would need to have postgres configured to log DDL, and set the log
> prefix to include timestamping, then you could scan those logs to get that
> information.its not otherwise stored in the database.
>
>
> Logging is the best way of capturing these events.

You can probably find out the relfilenode from pg_class for a given
relation name (relname) and then go to the datadirectory (or tablespace
directory) --> db directory (mapped to oid of pg_database) --> filename.
Check the date time of the file when it was created.

Though I don't think this infra has been built for this very purpose.


> --
> john r pierce, recycling bits in santa cruz
>
> --
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] How to view creation date and time of a relation

[John R Pierce]

On 5/19/2016 12:18 AM, Shrikant Bhende wrote:


Our application executes come scripts with the code consist of  DDL 
which creates lot of objects in the database in various schemas,also 
there are lot of connections firing the same code. I am able to locate 
the IP from where the script is initiated (which is causing more load 
on the database ), but I would like to know if I can pinpoint the 
relations which are created on a specific  date and time or else I can 
do something through which I get creation date and time of the objects.


you would need to have postgres configured to log DDL, and set the log 
prefix to include timestamping, then you could scan those logs to get 
that information.its not otherwise stored in the database.



--
john r pierce, recycling bits in santa cruz

[GENERAL] How to view creation date and time of a relation

[Shrikant Bhende]

Hi all,

Our application executes come scripts with the code consist of  DDL which
creates lot of objects in the database in various schemas,also there are
lot of connections firing the same code. I am able to locate the IP from
where the script is initiated (which is causing more load on the database
), but I would like to know if I can pinpoint the relations which are
created on a specific  date and time or else I can do something through
which I get creation date and time of the objects.

-- 
Shrikant Bhende
+91-9975543712