Re: [GENERAL] Incremental refresh - Materialized view

[Laurenz Albe]

Krithika Venkatesh wrote:
> I need to implement incremental refresh of materialized view.
> 
> Please let me know how to do the incremental refresh of materialized view in 
> postgresql 9.5.9 version. 
> 
> Is there anything similar to materialized view log in postgresql.

There is no such feature in PostgreSQL (yet), so you'll have to
do it yourself.

The "materialized view" would then be a regular table (with read only
access), and each underlying table would have a trigger that records
changes with a timestamp to a log table.

You can then write a function that brings the "materialized view"
up to date.

Yours,
Laurenz Albe


-- 
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] Incremental refresh - Materialized view

[Krithika Venkatesh]

Materialized view log is one of the feature in oracle. It creates a log in
which the changes made to the table are recorded. This log is required for
an asynchronous materialized view that is refreshed incrementally.

I read in the below link about incrementally refreshing the materialized
view in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

Thanks,
Krithika





On 07-Nov-2017 12:37 PM, "John R Pierce"  wrote:

On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:

> I need to implement incremental refresh of materialized view.
>
> Please let me know how to do the incremental refresh of materialized view
> in postgresql 9.5.9 version.
>
> Is there anything similar to materialized view log in postgresql.
>


you refresh a materialized view with REFRESH MATERIALIZED VIEW name;
There's no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a feature
of some other database server ?



-- 
john r pierce, recycling bits in santa cruz



-- 
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] Combine multiple text search configuration

[hmidi slim]

Hi,
Thank for your proposition but when to use this query :
(to_tsvector('english', document) || to_tsvector('french', document)) @@
(to_tsquery('english', query) || to_tsquery('french', query))
I think that the performance decrease and not a good solution for big
amount of data. Is it?


2017-11-06 20:46 GMT+01:00 Johannes Graën :

> Hi,
>
>
> On 2017-11-06 09:17, hmidi slim wrote:
> > Hi,
> > I want to know if I can combine multiple text search configurations when
> > I tried to use FTS.
> > Is there any options like this:
> > *to_tsvector(['english', 'french'], document)*
> > *
> > *
> > Trying to create a new text configuration:
> > *Create text search configuration test (copy=simple)*
> > *Alter text search configuration test*
> > *add mapping for asciiword with english_stem,french_stem*
> > *
> > *
> > This query doesn't work. How can I combine multiple text search
> > configurations if I need more than one into my query to search a word?
>
> what about using two indexes, one for each language? If your documents
> can either be English OR French, the English OR the French vector should
> match an English OR French tsquery.
>
> It is not clear to me how combining two stemmers should practically work
> since each word can only have one stem. If you have multilingual
> documents or texts with code switching, you could also try combining the
> two vectors both for the documents and the query:
>
> (to_tsvector('english', document) || to_tsvector('french', document)) @@
> (to_tsquery('english', query) || to_tsquery('french', query))
>
>

Re: [GENERAL] idle in transaction, why

[Thomas Kellerer]

Rob Sargent schrieb am 06.11.2017 um 23:09:
> Gosh I wish I could learn to proof-read my posts.
> My support crew graciously set
> 
> idle_transaction_timeout = 1
> 
> Now to ponder if I need zero or some large number.

The unit of that setting is milliseconds (if no unit is specified). 
zero disables that feature.

One millisecond seems like an awfully short period to allow a transaction to be 
idle. 

I would figure values in "minutes" to be more realistic depending on the 
workload and characteristics of the application. 

A transaction that has several seconds of "think time" between individual 
statements doesn't seem that unrealistic. 


Thomas




-- 
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] Incremental refresh - Materialized view

[John R Pierce]

On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:

I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized 
view in postgresql 9.5.9 version.


Is there anything similar to materialized view log in postgresql.



you refresh a materialized view with REFRESH MATERIALIZED VIEW name;    
There's no 'incremental' methods, as views can be quite complex.


I do not know what you mean by 'materialized view log', is this a 
feature of some other database server ?




--
john r pierce, recycling bits in santa cruz



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

[GENERAL] Incremental refresh - Materialized view

[Krithika Venkatesh]

Hi,

I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view
in postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.

Thanks in Advance!

Regards,
Krithika

Re: [GENERAL] Naming conventions for column names

[Sachin Kotwal]

Hi,


It seems people worrying about failure of client side code after changes in
column names.

Melvin also mention that just change in one column was broken many things.


>
> > My intension is to improve naming conventions and increase naming string
> > where naming conventions are correct but make shorten.
>
>
Sure. It make sense.
Definitely we can do this. Specially patch reviewers and committers need to
take care of this. (I am not ordering to anyone here, just putting my
thoughts)



> I think the proper amount of effort to rename existing system catalog
> columns is zero.
>
> Can you clarify how efforts will be zero. Is there any script in place or
automatic way of doing this?





> Also, I think it's pretty difficult to change column names on views that
> have already been released.  The compatibility break for existing tools
> is just too large.
>
>
It is correct. But then I think we can do below things:

1. Instead of pushing all changes at once , we can group them and push them
slowly in each major version release.
2. We need to decide weather we really need those changes in old versions?
3. Notify or highlight these changes in release notes because this can
break some existing tools and user code.
4. As Alvaro suggested, when developer working on any major code change, He
should give proper naming conventions to system catalog/view column names
in that area.



If It is impossible or not required to do this, then we can stop discussion
with 4th point made by Alvaro as conclusion of this discussion.


Please committers give their final view on this.

-- 

Thanks and Regards,
Sachin Kotwal

Re: [GENERAL] the database system is shutting down - terminating walsender process due to replication timeout

[Zarko Aleksic]

Thanks for you reply. In the meantime we figured out what the issue was. We had 
a virtual IP that was being released from the master server just as the master 
would try to stream the last checkpoint. This caused wal_sender process to hang 
until the timeout value was reached. Technically speaking, it was a networking 
problem, but not on the switch/router, rather on the host it self. Stopping the 
replication from standby to the (movable) master VIP was the solution that we 
went with.


Best regards,
Zarko


From: Tom Lane 
Sent: Wednesday, November 1, 2017 10:25 AM
To: Zarko Aleksic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] the database system is shutting down - terminating 
walsender process due to replication timeout

Zarko Aleksic  writes:
> I'm looking for a bit of help understanding a particular behavior we are 
> seeing with our PostgreSQL 9.6. After issuing a service shutdown command with 
> "systemctl stop" on RHEL 7 our PostgreSQL instance started behaving weirdly. 
> For the first time it wouldn't shutdown so easily / quickly.
> From the logs we could see that standby nodes that were trying to connect 
> were rejected due to database being shutdown. After wal_sender_timeout and 
> wal_receiver_timeout (default 60s) were reached the database finally shut 
> down. It seems that walsender process was preventing the shutdown of the 
> master database - until timeout was reached, a behavior we didn't experience 
> before.

9.6.what?

There were several possibly-relevant bug fixes in 9.6.3 and 9.6.4,
notably this one:

Author: Tom Lane 
Branch: master Release: REL_10_BR [fca85f8ef] 2017-06-30 12:00:15 -0400
Branch: REL9_6_STABLE Release: REL9_6_4 [e9d4aa594] 2017-06-30 12:00:03 -0400
Branch: REL9_5_STABLE Release: REL9_5_8 [446914f6b] 2017-06-30 12:00:03 -0400
Branch: REL9_4_STABLE Release: REL9_4_13 [5aa8db014] 2017-06-30 12:00:03 -0400

Fix walsender to exit promptly if client requests shutdown.

It's possible for WalSndWaitForWal to be asked to wait for WAL that doesn't
exist yet.  That's fine, in fact it's the normal situation if we're caught
up; but when the client requests shutdown we should not keep waiting.
The previous coding could wait indefinitely if the source server was idle.

In passing, improve the rather weak comments in this area, and slightly
rearrange some related code for better readability.

Back-patch to 9.4 where this code was introduced.

Discussion: https://postgr.es/m/14154.1498781...@sss.pgh.pa.us

I think that would only apply if the walsender's client had tried to
disconnect at the same time you were doing the master-server shutdown,
but maybe that's what happened.

There is still work going on around the walsender timeout, so maybe
what you hit is an as-yet-unresolved bug, but in any case you should
be keeping up with minor releases.

regards, tom lane

Re: [GENERAL] idle in transaction, why

[Rob Sargent]

On 11/06/2017 02:38 PM, Merlin Moncure wrote:

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent  wrote:


On 11/06/2017 01:41 PM, Tom Lane wrote:

Rob Sargent  writes:

idle_in_transaction_session_timeout | 0   | default |
|| A value of 0 turns off the timeout. | user

Meh.  I think we're barking up the wrong tree anyway: so far as I can
find, there is no error message reading 'idle transaction timeout'
in the existing PG sources (and I sure hope no committer would have
thought that such an ambiguous message text was satisfactory).
So I think your error is coming from client-side or third-party code.
What other moving parts have you got in there?

 regards, tom lane

The most likely culprit is JOOQ, which I chose as a learning experience
(normally I use ORM tools).  But that said, I just ran the same data into my
test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) and all went
swimmingly.  It's a sizable payload (several batches of over 100K items,
deserialized from json) and takes 5 minutes to save.

I was hoping to blame the virt or the beta.  Not a good time to start doubt
JOOQ

I can't speak to JOOQ (who are fantastic postgres supporters BTW), but
in the java world this typically comes from one of two things:

1) you have long running in-transaction process that has very heavy
computation between sql statements.  this is a rare case
--or--
2) you are connecting pooling and the app sent a connection back into
the pool without having a transaction committed.

"2" is a common and dangerous bug.  It can happen due to bug in
application code (most likely), the jdbc wrapping library code (less
likely), or the connection pooler itself if you're using one.  A
typical cause of application side problems is manual transaction
management and some uncaught exception paths where errors (say, a
duplicate key error).  So investigate causes like that first
(database errors in the database log might be a helpful clue) and go
from there.   If the problem is within JOOQ, you ought to take it up
with them, which I encourage you to do, since I consider JOOQ to be a
wonderful treatment of SQL integration from the java perspective.

merlin

Gosh I wish I could learn to proof-read my posts.
My support crew graciously set

   idle_transaction_timeout = 1

Now to ponder if I need zero or some large number.

Thanks again

Re: [GENERAL] idle in transaction, why

[Rob Sargent]

On 11/06/2017 02:38 PM, Merlin Moncure wrote:

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent  wrote:


On 11/06/2017 01:41 PM, Tom Lane wrote:

Rob Sargent  writes:

idle_in_transaction_session_timeout | 0   | default |
|| A value of 0 turns off the timeout. | user

Meh.  I think we're barking up the wrong tree anyway: so far as I can
find, there is no error message reading 'idle transaction timeout'
in the existing PG sources (and I sure hope no committer would have
thought that such an ambiguous message text was satisfactory).
So I think your error is coming from client-side or third-party code.
What other moving parts have you got in there?

 regards, tom lane

The most likely culprit is JOOQ, which I chose as a learning experience
(normally I use ORM tools).  But that said, I just ran the same data into my
test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) and all went
swimmingly.  It's a sizable payload (several batches of over 100K items,
deserialized from json) and takes 5 minutes to save.

I was hoping to blame the virt or the beta.  Not a good time to start doubt
JOOQ

I can't speak to JOOQ (who are fantastic postgres supporters BTW), but
in the java world this typically comes from one of two things:

1) you have long running in-transaction process that has very heavy
computation between sql statements.  this is a rare case
--or--
2) you are connecting pooling and the app sent a connection back into
the pool without having a transaction committed.

"2" is a common and dangerous bug.  It can happen due to bug in
application code (most likely), the jdbc wrapping library code (less
likely), or the connection pooler itself if you're using one.  A
typical cause of application side problems is manual transaction
management and some uncaught exception paths where errors (say, a
duplicate key error).  So investigate causes like that first
(database errors in the database log might be a helpful clue) and go
from there.   If the problem is within JOOQ, you ought to take it up
with them, which I encourage you to do, since I consider JOOQ to be a
wonderful treatment of SQL integration from the java perspective.

merlin


"2" definitely fits this bill.  The difference between test and prod is 
pgboucer which I've forgotten to mention at all in this thread.  I do 
start a tx in my code a la:


public void writedb(DSLContext ctx) {
logger.error("{}: start transaction at {}", getRunTag(),
   System.currentTimeMillis());
ctx.transaction(ltx -> {
startProcess(ctx);
writeSegments(ctx);
finishProcess(ctx);
});
logger.error("{}: end transaction at {}", getRunTag(),
   System.currentTimeMillis());
}

But I don't think this is out of the ordinary. However writing lists 
with up to 1,175,151 records might not be (2 this size, to at 131K). 
I'll take this up with JOOQ and pgbouncer.  (fasterxml is having trouble 
with this size too.  Not sure how I can break this up if need be done, 
they're all generate from the same analysis run.)


Thanks to all.

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

[David Pacheco]

On Mon, Nov 6, 2017 at 12:35 PM, Tom Lane  wrote:

> David Pacheco  writes:
> > ... that process appears to have exited due to a fatal error
> > (out of memory).  (I know it exited because the process still exists in
> the
> > kernel -- it hasn't been reaped yet -- and I think it ran out of memory
> > based on a log message I found from around the time when the process
> > exited.)
>
> Could we see the exact log message(s) involved?  It's pretty hard to
> believe that the logger would have consumed much memory.



Thanks for the quick reply!

Based on kernel state about the dead but unreaped syslogger process, I
believe the process exited at 2017-10-27T23:46:21.258Z.  Here are all of
the entries in the PostgreSQL log from 23:19:12 until the top of the next
hour:
https://gist.githubusercontent.com/davepacheco/c5541bb464532075f2da761dd990a457/raw/2ba242055aca2fb374e9118045a830d08c590e0a/gistfile1.txt

There's no log entry at exactly 23:46:21 or even immediately before that,
but there are a lot of "out of memory" errors and a FATAL one at 23:47:28.
Unfortunately, we haven't configured logging to include the pid, so I can't
be sure which messages came from the syslogger.

There are also many log entries for some very long SQL queries.  I'm sure
that contributed to this problem by filling up the pipe.  I was able to
extract the contents of the pipe while the system was hung, and it was more
of these giant query strings.

I think it's likely that this database instance was running in a container
with way too small a memory cap for the number of processes configured.
(This was a zone (a lightweight container) allocated with 2GB of memory and
configured with 512MB of shared_buffers and up to 200 connections.)  I
expect that the system got to a persistent state of having basically no
memory available, at which point nearly any attempt to allocate memory
could fail.  The syslogger itself may not have been using much memory.

So I'm not so much worried about the memory usage itself, but it would be
nice if this condition were handled better.  Handling out-of-memory is
obviously hard, especially when it means being unable to fork, but even
crashing would have been better for our use-case.  And of course, there are
other reasons that the syslogger could exit prematurely besides being low
on memory, and those might be more recoverable.

Thanks,
Dave

Re: [GENERAL] idle in transaction, why

[Merlin Moncure]

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent  wrote:
>
>
> On 11/06/2017 01:41 PM, Tom Lane wrote:
>>
>> Rob Sargent  writes:
>>>
>>>idle_in_transaction_session_timeout | 0   | default |
>>> || A value of 0 turns off the timeout. | user
>>
>> Meh.  I think we're barking up the wrong tree anyway: so far as I can
>> find, there is no error message reading 'idle transaction timeout'
>> in the existing PG sources (and I sure hope no committer would have
>> thought that such an ambiguous message text was satisfactory).
>> So I think your error is coming from client-side or third-party code.
>> What other moving parts have you got in there?
>>
>> regards, tom lane
>
> The most likely culprit is JOOQ, which I chose as a learning experience
> (normally I use ORM tools).  But that said, I just ran the same data into my
> test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) and all went
> swimmingly.  It's a sizable payload (several batches of over 100K items,
> deserialized from json) and takes 5 minutes to save.
>
> I was hoping to blame the virt or the beta.  Not a good time to start doubt
> JOOQ

I can't speak to JOOQ (who are fantastic postgres supporters BTW), but
in the java world this typically comes from one of two things:

1) you have long running in-transaction process that has very heavy
computation between sql statements.  this is a rare case
--or--
2) you are connecting pooling and the app sent a connection back into
the pool without having a transaction committed.

"2" is a common and dangerous bug.  It can happen due to bug in
application code (most likely), the jdbc wrapping library code (less
likely), or the connection pooler itself if you're using one.  A
typical cause of application side problems is manual transaction
management and some uncaught exception paths where errors (say, a
duplicate key error).  So investigate causes like that first
(database errors in the database log might be a helpful clue) and go
from there.   If the problem is within JOOQ, you ought to take it up
with them, which I encourage you to do, since I consider JOOQ to be a
wonderful treatment of SQL integration from the java perspective.

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] idle in transaction, why

[Rob Sargent]

On 11/06/2017 01:50 PM, Rob Sargent wrote:



On 11/06/2017 01:41 PM, Tom Lane wrote:

Rob Sargent  writes:

   idle_in_transaction_session_timeout | 0   | default |
|| A value of 0 turns off the timeout. | user

Meh.  I think we're barking up the wrong tree anyway: so far as I can
find, there is no error message reading 'idle transaction timeout'
in the existing PG sources (and I sure hope no committer would have
thought that such an ambiguous message text was satisfactory).
So I think your error is coming from client-side or third-party code.
What other moving parts have you got in there?

regards, tom lane
The most likely culprit is JOOQ, which I chose as a learning 
experience (normally I use ORM tools).  But that said, I just ran the 
same data into my test env, (postgres 10.0 (real) on centos 6.9, 
ubuntu client) and all went swimmingly.  It's a sizable payload 
(several batches of over 100K items, deserialized from json) and takes 
5 minutes to save.


I was hoping to blame the virt or the beta.  Not a good time to start 
doubt JOOQ

My bet is that those 'org.postgres' messages came from the jdbc driver.


--
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] idle in transaction, why

[Rob Sargent]

On 11/06/2017 01:41 PM, Tom Lane wrote:

Rob Sargent  writes:

   idle_in_transaction_session_timeout | 0   | default |
|| A value of 0 turns off the timeout. | user

Meh.  I think we're barking up the wrong tree anyway: so far as I can
find, there is no error message reading 'idle transaction timeout'
in the existing PG sources (and I sure hope no committer would have
thought that such an ambiguous message text was satisfactory).
So I think your error is coming from client-side or third-party code.
What other moving parts have you got in there?

regards, tom lane
The most likely culprit is JOOQ, which I chose as a learning experience 
(normally I use ORM tools).  But that said, I just ran the same data 
into my test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) 
and all went swimmingly.  It's a sizable payload (several batches of 
over 100K items, deserialized from json) and takes 5 minutes to save.


I was hoping to blame the virt or the beta.  Not a good time to start 
doubt JOOQ



--
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] idle in transaction, why

[Tom Lane]

Rob Sargent  writes:
>   idle_in_transaction_session_timeout | 0   | default |
> || A value of 0 turns off the timeout. | user

Meh.  I think we're barking up the wrong tree anyway: so far as I can
find, there is no error message reading 'idle transaction timeout'
in the existing PG sources (and I sure hope no committer would have
thought that such an ambiguous message text was satisfactory).
So I think your error is coming from client-side or third-party code.
What other moving parts have you got in there?

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] postmaster deadlock while logging after syslogger exited

[Tom Lane]

David Pacheco  writes:
> I ran into what appears to be a deadlock in the logging subsystem.  It
> looks like what happened was that the syslogger process exited because it
> ran out of memory.  But before the postmaster got a chance to handle the
> SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
> That also failed, and the postmaster went to log a message about it, but
> it's blocked on the pipe that's normally connected to the syslogger,
> presumably because the pipe is full because the syslogger is gone and
> hasn't read from it.

Ugh.

> ... that process appears to have exited due to a fatal error
> (out of memory).  (I know it exited because the process still exists in the
> kernel -- it hasn't been reaped yet -- and I think it ran out of memory
> based on a log message I found from around the time when the process
> exited.)

Could we see the exact log message(s) involved?  It's pretty hard to
believe that the logger would have consumed much memory.

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] idle in transaction, why

[Rob Sargent]

On 11/06/2017 01:17 PM, Tom Lane wrote:

"David G. Johnston"  writes:

You should probably login as your application user and do "show
idle_in_transaction_session_timeout" to see what a clean session has for a
value and then figure out from there where that value is coming from.

You don't have to guess about the latter: the pg_settings view will tell
you exactly where the active value came from.  See the source, sourcefile,
sourceline columns.

regards, tom lane
select name, setting, source, sourcefile, sourceline, extra_desc, 
context from pg_settings where name ~ 'idle';
name | setting | source  | sourcefile | 
sourceline |  extra_desc   | context

-+-+-+++---+-
 idle_in_transaction_session_timeout | 0   | default |
|| A value of 0 turns off the timeout. | user
 tcp_keepalives_idle | 7200| default |
|| A value of 0 uses the system default. | user

(2 rows)



--
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] idle in transaction, why

[Rob Sargent]

On 11/06/2017 01:09 PM, David G. Johnston wrote:
On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent >wrote:


Using postgres 10-beta3 (hopefully 10.0 this week) on virtual
CentOS7 and this JDBC driver postgresql:42.1.4


The postgresql.conf file has

#idle_in_transaction_session_timeout = 0# in
milliseconds, 0 is disabled


​There are numerous places where default settings can be configured.

https://www.postgresql.org/docs/10/static/config-setting.html

You should probably login as your application user and do "show 
idle_in_transaction_session_timeout" to see what a clean session has 
for a value and then figure out from there where that value is coming 
from.


David J.


From logging in with the application role I get

   coon=> show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
   -
 0
   (1 row)

Re: [GENERAL] idle in transaction, why

[Tom Lane]

"David G. Johnston"  writes:
> You should probably login as your application user and do "show
> idle_in_transaction_session_timeout" to see what a clean session has for a
> value and then figure out from there where that value is coming from.

You don't have to guess about the latter: the pg_settings view will tell
you exactly where the active value came from.  See the source, sourcefile,
sourceline columns.

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] idle in transaction, why

[David G. Johnston]

On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent  wrote:

> Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and
> this JDBC driver postgresql:42.1.4
>
>
> The postgresql.conf file has
>
> #idle_in_transaction_session_timeout = 0# in milliseconds, 0 is
> disabled
>
>
​There are numerous places where default settings can be configured.

https://www.postgresql.org/docs/10/static/config-setting.html

You should probably login as your application user and do "show
idle_in_transaction_session_timeout" to see what a clean session has for a
value and then figure out from there where that value is coming from.

David J.

[GENERAL] postmaster deadlock while logging after syslogger exited

[David Pacheco]

Hello,

I ran into what appears to be a deadlock in the logging subsystem.  It
looks like what happened was that the syslogger process exited because it
ran out of memory.  But before the postmaster got a chance to handle the
SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum worker.
That also failed, and the postmaster went to log a message about it, but
it's blocked on the pipe that's normally connected to the syslogger,
presumably because the pipe is full because the syslogger is gone and
hasn't read from it.  The net result is that new connections to PostgreSQL
hang, and any query that causes PostgreSQL to log appears to hang, though
queries appear to work on existing connections as long as they do not cause
the backend to log anything.  I don't believe the system would ever get out
of this state.

I ran into this with version 9.2.4 on SmartOS (illumos), but it doesn't
look to me like any of the relevant code has changed in 9.6 or 10, nor is
it platform-specific.  Version 10.0 still:

- restarts the syslogger in response to a SIGCHLD signal (in "reaper")
- starts autovacuum workers in response to SIGUSR1 (in "sigusr1_handler")
- mask SIGCHLD at the beginning of the SIGUSR1 handler
- potentially logs from StartAutoVacWorker (and, of course, many other
places in the postmaster)

which I think means the problem is still possible.  Am I missing
something?  I couldn't find anything about this issue.  Is this just a
rarely seen bug?



Here's the raw data: my initial observation was that when I ran "psql" to
connect to the database, it would hang (for hours, it turned out).  My
postmaster process was blocked here, writing to its own stderr pipe:

$ mdb core.41349
Loading modules: [ libumem.so.1 libc.so.1 ld.so.1 ]
> $C
fd7fffdf9df0 libc.so.1`__write+0xa()
fd7fffdfb220 write_pipe_chunks+0x142()
fd7fffdfb280 send_message_to_server_log+0x55b()
fd7fffdfb2a0 EmitErrorReport+0xe6()
fd7fffdfb390 errfinish+0x1ac()
fd7fffdfb3b0 StartAutoVacWorker+0x5c()
fd7fffdfb3e0 StartAutovacuumWorker+0x6f()
fd7fffdfb410 sigusr1_handler+0x185()
fd7fffdfb420 libc.so.1`__sighndlr+6()
fd7fffdfb4b0 libc.so.1`call_user_handler+0x1db(10, 0, fd7fffdfb520)
fd7fffdfb500 libc.so.1`sigacthandler+0x116(10, 0, fd7fffdfb520)
fd7fffdfb970 libc.so.1`__pollsys+0xa()
fd7fffdfba90 libc.so.1`pselect+0x1cb(5, fd7fffdfbaf0, 0, 0,
fd7fffdfbaa0, 0)
fd7fffdfbae0 libc.so.1`select+0x5a(5, fd7fffdfbaf0, 0, 0,
fd7fffdfdaf0)
fd7fffdffb40 ServerLoop+0xb5()
fd7fffdffbe0 PostmasterMain+0xec2()
fd7fffdffc00 main+0x23a()
fd7fffdffc10 _start+0x6c()

>From inspecting kernel state, I found that the only other file descriptor
on the system that corresponds to the other side of this pipe is in the
postmaster itself, which it keeps open in case it needs to restart the
syslogger.  Based on the code, I expected the syslogger subprocess to have
the pipe open, but that process appears to have exited due to a fatal error
(out of memory).  (I know it exited because the process still exists in the
kernel -- it hasn't been reaped yet -- and I think it ran out of memory
based on a log message I found from around the time when the process
exited.)  Then I expected the postmaster would have restarted it, but I
found that it currently has SIGCHLD both pending and masked:

$ pflags 41349
41349:  /opt/postgresql/9.2.4/bin/postgres -D /manatee/pg/data
data model = _LP64  flags = ORPHAN|MSACCT|MSFORK
sigpend = 0x00028000,0x,0x
 /1:flags = ASLEEP  write(0x2,0xfd7fffdf9e10,0x65)
sigmask = 0xfebff047,0xfff7,0x03ff

The signal mask may be system-specific, but I've decoded it and confirmed
that SIGCHLD is in "sigpend" as well as "sigmask".  And that makes sense
because the stack above indicates we're in "sigusr1_handler", which masks
SIGCHLD when it starts.

If it wasn't clear from the above, the logging configuration looks like
this:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/pg/'
log_filename = 'postgresql.log'

Thanks,
Dave

Re: [GENERAL] Combine multiple text search configuration

[Johannes Graën]

Hi,


On 2017-11-06 09:17, hmidi slim wrote:
> Hi,
> I want to know if I can combine multiple text search configurations when
> I tried to use FTS.
> Is there any options like this:
> *to_tsvector(['english', 'french'], document)*
> *
> *
> Trying to create a new text configuration:
> *Create text search configuration test (copy=simple)*
> *Alter text search configuration test*
> *add mapping for asciiword with english_stem,french_stem*
> *
> *
> This query doesn't work. How can I combine multiple text search
> configurations if I need more than one into my query to search a word?

what about using two indexes, one for each language? If your documents
can either be English OR French, the English OR the French vector should
match an English OR French tsquery.

It is not clear to me how combining two stemmers should practically work
since each word can only have one stem. If you have multilingual
documents or texts with code switching, you could also try combining the
two vectors both for the documents and the query:

(to_tsvector('english', document) || to_tsvector('french', document)) @@
(to_tsquery('english', query) || to_tsquery('french', query))



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

[GENERAL] idle in transaction, why

[Rob Sargent]

Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 
and this JDBC driver postgresql:42.1.4



The postgresql.conf file has

   #idle_in_transaction_session_timeout = 0# in milliseconds, 0
   is disabled

my db url has "?prepareThreshold=0" since I bump into "already defined" 
statements otherwise


but I'm getting

   org.jooq.exception.DataAccessException: SQL [select
   "projectfile"."id", "projectfile"."name", "projectfile"."filetype",
   "projectfile"."uri", "projectfile"."people_id" from "projectfile"
   where "projectfile"."uri" = ?]; ERROR: idle transaction timeout

   

   2017-11-06T11:32:20-07:00  -  -  - Caused by:
   org.postgresql.util.PSQLException: ERROR: idle transaction timeout
   2017-11-06T11:32:20-07:00  -  -  - at
   
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
   2017-11-06T11:32:20-07:00  -  -  - at
   
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
   2017-11-06T12:12:43-07:00  -  -  - at
   org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
   2017-11-06T12:12:43-07:00  -  -  - at
   org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
   2017-11-06T12:12:43-07:00  -  -  - at
   org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
   2017-11-06T12:12:43-07:00  -  -  - at
   
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
   2017-11-06T12:12:43-07:00  -  -  - at
   org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)

at various calls, sometimes the first one, sometime several calls later 
(some select, some insert), all in a transaction initiated in my code. 
From what I can piece together from the bind values all the queries are 
sound - at least the selects expected to find things work as expected.



Where should I be looking?

Re: [GENERAL] Postgresql 9.3 service doesn't start on RedHat Linux 6.8

[John R Pierce]

On 11/6/2017 5:28 AM, Vikas Sharma wrote:
I am having issues in starting up postgresql service on RHEL 6.8, It 
was all working fine yesterday but now it doesn't start and throws 
error - same kind of error that is received connecting to remote 
postgres database.


what /exact/ error is it throwing ?



for the meantime I have started postgresql as below -
/usr/pgsql-9.3/bin/postmaster  -D /var/lib/pgsql/9.3/data &
and it is working fine.

Can someone let me know where to look at for why I can't start the 
service?


whats in /var/lib/pgsql/9.3/pgstartup.log  and 
/var/lib/pgsql/9.3/data/(latest).log  ?



--
john r pierce, recycling bits in santa cruz



--
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] Naming conventions for column names

[Alvaro Herrera]

Sachin Kotwal wrote:

> I believe these naming conventions will be at two levels:
> 
> 1. Internal code of PostgreSQL , structures getting used internally
> 2. SQL/C functions get executed at the time of database initialization to
> create default objects and system catalogs.
> 
> 
> I will see how much modifications/efforts need to be done and will come
> back again if it is feasible.
> 
> My intension is to improve naming conventions and increase naming string
> where naming conventions are correct but make shorten.

I think the proper amount of effort to rename existing system catalog
columns is zero.

Also, I think it's pretty difficult to change column names on views that
have already been released.  The compatibility break for existing tools
is just too large.

A valuable service would be to ensure that any new views, and new
columns on existing views, have sensible names.

-- 
Álvaro Herrerahttps://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

Re: [GENERAL] Naming conventions for column names

[Melvin Davidson]

On Mon, Nov 6, 2017 at 10:04 AM, Karsten Hilbert 
wrote:

> On Mon, Nov 06, 2017 at 08:23:07PM +0530, Sachin Kotwal wrote:
>
> > You are right. Those naming conventions are old and that is why we have
> to
> > improve those where ever and when ever required.
>
> I'd love to see the "requirement" defined.
>
> Regards,
> 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
>

*All,*

*Please take note that when PG 9.2 was release, a column rename of*
*"procpid" to just "pid" in pg_stat_activity DID break prior user code,*
*therefore I must strongly advise against any other unnecessary column*

*renames in system catalogs.*

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

Re: [GENERAL] Naming conventions for column names

[Karsten Hilbert]

On Mon, Nov 06, 2017 at 08:23:07PM +0530, Sachin Kotwal wrote:

> You are right. Those naming conventions are old and that is why we have to
> improve those where ever and when ever required.

I'd love to see the "requirement" defined.

Regards,
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] xmin increasing within a transaction block?

[Luca Ferrari]

On Mon, Nov 6, 2017 at 3:26 PM, Tom Lane  wrote:
> Alvaro Herrera  writes:
>> Luca Ferrari wrote:
>>> Why is xmin greater than the current transaction id (and most notably
>>> not "fixed")?
>
>> Something is using subtransactions there.  My first guess would be that
>> there are triggers with EXCEPTION blocks, but your example doesn't show
>> any.  Or maybe you have event triggers.
>
> I can reproduce the example if I "\set ON_ERROR_ROLLBACK on" in psql.
>


Shame on me, I did forgot to have enabled that in my ~/.psqlrc file
(and did not hit an error within the transaction block to see it was
aborting). And in fact, the manual page for psql reports that
ON_ERROR_ROLLBACK:

The error rollback mode works by issuing an implicit SAVEPOINT for you,
just before each command that is in a transaction block, and
then rolling back to the savepoint if the command fails.

Sorry for the noise.
Thanks,
Luca


-- 
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] Naming conventions for column names

[Sachin Kotwal]

Hi Tom,


You are right. Those naming conventions are old and that is why we have to
improve those where ever and when ever required.


If no one has objection, I will give a try to improve this part.

I believe these naming conventions will be at two levels:

1. Internal code of PostgreSQL , structures getting used internally
2. SQL/C functions get executed at the time of database initialization to
create default objects and system catalogs.


I will see how much modifications/efforts need to be done and will come
back again if it is feasible.

My intension is to improve naming conventions and increase naming string
where naming conventions are correct but make shorten.


Suggestions and feedbacks are welcome.



Regards,
Sachin Kotwal





On Mon, Nov 6, 2017 at 8:03 PM, Tom Lane  wrote:

> Sachin Kotwal  writes:
> > I can understand that it is important to maintain naming pattern same as
> > system catalogs, but in that case we may need to redefine system catalogs
> > naming conventions .
>
> Those naming conventions are twenty-five years old, and there is an
> astonishing amount of client code that would break if we ran around
> changing existing system catalog column names.  It's very unlikely that
> any proposal to do that would even receive serious consideration.
>
> The bar to using new naming conventions in new catalogs would be
> lower, of course, but then you have to think about the confusion
> factor of having different naming styles in different places.
>
> regards, tom lane
>



-- 

Thanks and Regards,
Sachin Kotwal

Re: [GENERAL] Naming conventions for column names

[Rakesh Kumar]

>Those naming conventions are twenty-five years old, and there is an
>astonishing amount of client code that would break if we ran around
>changing existing system catalog column names.  It's very unlikely that
>any proposal to do that would even receive serious consideration.

>The bar to using new naming conventions in new catalogs would be
>lower, of course, but then you have to think about the confusion
>factor of having different naming styles in different places.

Isn't there a word for the above : Technology debt :-)


-- 
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] Naming conventions for column names

[Tom Lane]

Sachin Kotwal  writes:
> I can understand that it is important to maintain naming pattern same as
> system catalogs, but in that case we may need to redefine system catalogs
> naming conventions .

Those naming conventions are twenty-five years old, and there is an
astonishing amount of client code that would break if we ran around
changing existing system catalog column names.  It's very unlikely that
any proposal to do that would even receive serious consideration.

The bar to using new naming conventions in new catalogs would be
lower, of course, but then you have to think about the confusion
factor of having different naming styles in different places.

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] xmin increasing within a transaction block?

[Tom Lane]

Alvaro Herrera  writes:
> Luca Ferrari wrote:
>> Why is xmin greater than the current transaction id (and most notably
>> not "fixed")?

> Something is using subtransactions there.  My first guess would be that
> there are triggers with EXCEPTION blocks, but your example doesn't show
> any.  Or maybe you have event triggers.

I can reproduce the example if I "\set ON_ERROR_ROLLBACK on" in psql.

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] Converting AGE() to something human readable

[Ron Johnson]

Hi,

How is this done in v8.4?

postgres=# SELECT datname, datfrozenxid, age(datfrozenxid)
postgres-# FROM pg_database;
  datname  | datfrozenxid |    age
---+--+---
template1 |   3603334165 |  25735089
template0 |   3603470462 |  25598792
postgres  |   3576970250 |  52099004
TAPd  |   3489165829 | 139903425
(4 rows)


--
World Peace Through Nuclear Pacification



--
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] xmin increasing within a transaction block?

[Alvaro Herrera]

Luca Ferrari wrote:

> Any other idea?

None here.  Maybe try attaching a debugger, setting a breakpoint on
AssignTransactionId, and grab backtraces when it is hit.

-- 
Álvaro Herrerahttps://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

Re: [GENERAL] xmin increasing within a transaction block?

[Luca Ferrari]

On Mon, Nov 6, 2017 at 2:29 PM, Andres Freund  wrote:
> That doesn't look like plain postgres behaviour to me. Any chance you're
> using a pooler in statement mode in front of postgres?


None I'm aware of, since the machine is using postgresql locally and
I'm connecting to it using the port 5432.
I did have in the past enabled wal_level to logical but now it is set
to minimal and I don't have any replication slot (in the case it could
do something related to this behvior):

# SELECT * FROM pg_replication_slots;
(0 rows)

Any idea?
Luca


-- 
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] xmin increasing within a transaction block?

[Luca Ferrari]

On Mon, Nov 6, 2017 at 1:53 PM, Alvaro Herrera  wrote:
> Something is using subtransactions there.  My first guess would be that
> there are triggers with EXCEPTION blocks, but your example doesn't show
> any.  Or maybe you have event triggers.

Thanks, but I don't see any event trigger:

> \dy
  List of event triggers
 Name | Event | Owner | Enabled | Procedure | Tags
--+---+---+-+---+--
(0 rows)


Please note that I tested it on a freshly created database obtaining
the same behavior.
I did have in the past event trigger, but I have dropped long time
before this behavior.

Any other idea?

Luca


-- 
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] Naming conventions for column names

[Sachin Kotwal]

Hi Peter,

I can understand that it is important to maintain naming pattern same as
system catalogs, but in that case we may need to redefine system catalogs
naming conventions .

So that we can use those newly added naming conventions in system views as
well.

It is difficult to understand usename = database user name and usesysid =
system user id.
It is better to use full names to those columns, so that Users can easily
understand those columns by their names.

In this case adding one or more laters will not cause any problem but will
add more readability.
In case adding 3 letters indicating the catalog , can we use 4 or 5 letters
?

I think we need to rethink about these short naming conventions which are
making confusion, If community is ok with that.

Please share yours thoughts on this.


Regards,
Sachin Kotwal







On Mon, Nov 6, 2017 at 6:21 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 11/6/17 05:36, Sachin Kotwal wrote:
> > Is there any special reason to keep column names as usesysid
> > and usename instead of usersysid and username in below system View?
>
> The reason to *keep* them is compatibility.  The reason they are like
> that to start with is because that is the naming pattern used in the
> system catalogs: 3 letters indicating the catalog, plus additional
> letters or words.  It is useful to use the same name in views such as
> pg_stat_replication, so you can easily join different views and catalogs.
>
> --
> Peter Eisentraut  http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>



-- 

Thanks and Regards,
Sachin Kotwal

Re: [GENERAL] xmin increasing within a transaction block?

[Andres Freund]

Hi,

On 2017-11-06 10:36:06 +0100, Luca Ferrari wrote:
> Hi all,
> I suspect this has a trivial explaination, but this is what I'm experiencing:
> 
> > CREATE TABLE foo( i int );
> > BEGIN;
> * > INSERT INTO foo(i) VALUES( 1 );
> * > INSERT INTO foo(i) VALUES( 2 );
> * > SELECT xmin, cmin, xmax, cmax, i FROM foo;
>  xmin | cmin | xmax | cmax | i
> --+--+--+--+---
>  2466 |0 |0 |0 | 1
>  2467 |1 |0 |1 | 2
> (2 rows)
> 
> * > SELECT txid_current();
>  txid_current
> --
>  2465
> (1 row)
> 
> 
> Why is xmin greater than the current transaction id (and most notably
> not "fixed")?
> What am I missing here?
> I'm running 9.6.5.

That doesn't look like plain postgres behaviour to me. Any chance you're
using a pooler in statement mode in front of postgres?

Greetings,

Andres Freund


-- 
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 9.3 service doesn't start on RedHat Linux 6.8

[Vikas Sharma]

Hi,

I am having issues in starting up postgresql service on RHEL 6.8, It was
all working fine yesterday but now it doesn't start and throws error - same
kind of error that is received connecting to remote postgres database.

for the meantime I have started postgresql as below -
/usr/pgsql-9.3/bin/postmaster  -D /var/lib/pgsql/9.3/data  &
and it is working fine.

Can someone let me know where to look at for why I can't start the service?

Thanks
Vikas

Re: [GENERAL] Naming conventions for column names

[Peter Eisentraut]

On 11/6/17 05:36, Sachin Kotwal wrote:
> Is there any special reason to keep column names as usesysid
> and usename instead of usersysid and username in below system View?

The reason to *keep* them is compatibility.  The reason they are like
that to start with is because that is the naming pattern used in the
system catalogs: 3 letters indicating the catalog, plus additional
letters or words.  It is useful to use the same name in views such as
pg_stat_replication, so you can easily join different views and catalogs.

-- 
Peter Eisentraut  http://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

Re: [GENERAL] xmin increasing within a transaction block?

[Alvaro Herrera]

Luca Ferrari wrote:
> Hi all,
> I suspect this has a trivial explaination, but this is what I'm experiencing:
> 
> > CREATE TABLE foo( i int );
> > BEGIN;
> * > INSERT INTO foo(i) VALUES( 1 );
> * > INSERT INTO foo(i) VALUES( 2 );
> * > SELECT xmin, cmin, xmax, cmax, i FROM foo;
>  xmin | cmin | xmax | cmax | i
> --+--+--+--+---
>  2466 |0 |0 |0 | 1
>  2467 |1 |0 |1 | 2
> (2 rows)

With this example both rows show the same xmin to me, which is what I'd
expect.

> Why is xmin greater than the current transaction id (and most notably
> not "fixed")?

Something is using subtransactions there.  My first guess would be that
there are triggers with EXCEPTION blocks, but your example doesn't show
any.  Or maybe you have event triggers.

-- 
Álvaro Herrerahttps://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

Re: [GENERAL] Naming conventions for column names

[Sachin Kotwal]

Hi All,

Correcting my words.

Is there any special reason to keep column names as usesysid
and usename instead of usersysid and username in below system View?



On Mon, Nov 6, 2017 at 4:03 PM, Sachin Kotwal  wrote:

> Hi All,
>
> Is there any reason to keep column names as usesysid and senate instead of
> usersysid and username ?
>
>
>
> postgres=# select * from pg_stat_replication ;
>  pid  | usesysid | usename | application_name |  client_addr  |
> client_hostname | client_port | backend_start |
> backend_xmin |   stat
> e   | sent_location | write_location | flush_location | replay_location |
> sync_priority | sync_state
> --+--+-+--+-
> --+-+-+-
> --+--+---
> +---+++-
> +---+
>  2297 |24522 | replica | walreceiver  | 192.168.16.53 |
>   |   49782 | 2017-08-15 00:45:43.256404-04 |14938 | stream
> ing | 111/BD9D5328  | 111/BD9D5328   | 111/BD9D5328   | 111/BD9D52F0|
> 0 | async
> (1 row)
>
>
>
>
> --
>
> Thanks and Regards,
> Sachin Kotwal
>



-- 

Thanks and Regards,
Sachin Kotwal

[GENERAL] Naming conventions for column names

[Sachin Kotwal]

Hi All,

Is there any reason to keep column names as usesysid and senate instead of
usersysid and username ?



postgres=# select * from pg_stat_replication ;
 pid  | usesysid | usename | application_name |  client_addr  |
client_hostname | client_port | backend_start |
backend_xmin |   stat
e   | sent_location | write_location | flush_location | replay_location |
sync_priority | sync_state
--+--+-+--+---+-+-+---+--+---
+---+++-+---+
 2297 |24522 | replica | walreceiver  | 192.168.16.53 |
|   49782 | 2017-08-15 00:45:43.256404-04 |14938 | stream
ing | 111/BD9D5328  | 111/BD9D5328   | 111/BD9D5328   | 111/BD9D52F0|
  0 | async
(1 row)




-- 

Thanks and Regards,
Sachin Kotwal

[GENERAL] xmin increasing within a transaction block?

[Luca Ferrari]

Hi all,
I suspect this has a trivial explaination, but this is what I'm experiencing:

> CREATE TABLE foo( i int );
> BEGIN;
* > INSERT INTO foo(i) VALUES( 1 );
* > INSERT INTO foo(i) VALUES( 2 );
* > SELECT xmin, cmin, xmax, cmax, i FROM foo;
 xmin | cmin | xmax | cmax | i
--+--+--+--+---
 2466 |0 |0 |0 | 1
 2467 |1 |0 |1 | 2
(2 rows)

* > SELECT txid_current();
 txid_current
--
 2465
(1 row)


Why is xmin greater than the current transaction id (and most notably
not "fixed")?
What am I missing here?
I'm running 9.6.5.

> select version();

version
--
 PostgreSQL 9.6.5 on amd64-portbld-freebsd11.0, compiled by FreeBSD
clang version 3.8.0 (tags/RELEASE_380/final 262564) (based on LLVM
3.8.0), 64-bit
(1 row)


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

[GENERAL] Combine multiple text search configuration

[hmidi slim]

Hi,
I want to know if I can combine multiple text search configurations when I
tried to use FTS.
Is there any options like this:
*to_tsvector(['english', 'french'], document)*

Trying to create a new text configuration:
*Create text search configuration test (copy=simple)*
*Alter text search configuration test*
*add mapping for asciiword with english_stem,french_stem*

This query doesn't work. How can I combine multiple text search
configurations if I need more than one into my query to search a word?