Re: [GENERAL] How to speed up pg_trgm / gin index scan
On 28/07/15 16:42, Merlin Moncure wrote: Great stuff! Sorry Oleg I don't have your original message anymore and can't reply into the right place in the thread, so I took the liberty to CC: you. There are some more big optimizations (via Jeff Janes) coming down the pike for trigram searching. See thread: http://www.postgresql.org/message-id/CAMkU=1wor_pdmie6d-zj6sdopihd_iue3vzsxfge_i4-aqy...@mail.gmail.com. I think it should be possible to patch the 9.4 pg_trgm module with Jeff's stuff -- it might be worthwhile to do that and run some tests and report back. I don't know if they address your particular case but in some situations the speedups are really dramatic. merlin On 28/07/15 16:45, Arthur Silva wrote: You could experiment recompiling pg_trgm commenting out the KEEPONLYALNUM and/or IGNORECASE definitions if you are looking for exact matches, this will increase the index size but will make it more selective. Also, there's a thread around for pg_trgrm 1.2 which will get you even more boost. Thanks for the hints and all the hard work you guys are putting into this. I'll follow the further development closely and report back if we get any new breakthroughs with this rather big data set. Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Synchronous replication and read consistency
Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] instr detail
Hi All,
is instr available in postgres 9.3..?
in oracle instr('12.32.42','.',-1) ,any help appreciated
Re: [GENERAL] instr detail
On Wed, Jul 29, 2015 at 10:03:56PM +0530, Ramesh T wrote:
Hi All,
is instr available in postgres 9.3..?
in oracle instr('12.32.42','.',-1) ,
any ...
http://www.postgresql.org/docs/9.3/static/index.html
... help appreciated
You are welcome !
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] Synchronous replication and read consistency
Kevin Grittner schrieb am 29.07.2015 um 23:10: No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is normally quite small; you may have trouble running into a case where you notice it, but it can happen. It's actually not that hard to run into. We encountered this when we were running unit tests against a master/slave setup with pgPool: http://postgresql.nabble.com/Synchronous-replication-pgPool-not-all-transactions-immediately-visible-on-standby-tp5820275.html Regards 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] instr detail
Based om the definition of Oracle instr(), the equivalent PostgreSQL
function would be
position(substring in string).
On Wed, Jul 29, 2015 at 3:11 PM, Igor Neyman iney...@perceptron.com wrote:
*From:* pgsql-general-ow...@postgresql.org [mailto:
pgsql-general-ow...@postgresql.org] *On Behalf Of *Ramesh T
*Sent:* Wednesday, July 29, 2015 12:34 PM
*To:* pgsql-general@postgresql.org
*Subject:* [GENERAL] instr detail
Hi All,
is instr available in postgres 9.3..?
in oracle instr('12.32.42','.',-1) ,any help appreciated
__
There are lots of string functions and operators:
http://www.postgresql.org/docs/9.3/static/functions-string.html
There is definitely a replacement for Oracle’s instr(…).
Regards,
Igor Neyman
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] Synchronous replication and read consistency
Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. Joshua: THis essentially contradicts your statement to me. On Wed, Jul 29, 2015 at 5:10 PM, Kevin Grittner kgri...@ymail.com wrote: Ravi Krishna sravikrish...@gmail.com wrote: As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is normally quite small; you may have trouble running into a case where you notice it, but it can happen. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. One of the issues is that with the current guarantee you need multiple replicas to prevent a failure of a standby from stalling the primary indefinitely, and you don't have an easy way to know *which* replica succeeded in persisting the transaction without doing a lot of work. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about copy from with timestamp format
Hi, I wanted to copy a file from local file system to postgres. I have timestamp value specified as: MMDDHH24 format -- for example: 2015072913 -- is July 29, 2015 at 13:00 how do I import this data into a timestamp field? thanks, murali. PS: I believe if I need the hour, I need to use timestamp (I do not want to put the hour as a separate column). I believe date datatype does not work, if I am right??
Re: [GENERAL] Synchronous replication and read consistency
On 07/29/2015 02:27 PM, Ravi Krishna wrote: Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. Joshua: THis essentially contradicts your statement to me. I would trust Kevin with this particular information. What he wrote was new to me as well. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] instr detail
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ramesh T
Sent: Wednesday, July 29, 2015 12:34 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] instr detail
Hi All,
is instr available in postgres 9.3..?
in oracle instr('12.32.42','.',-1) ,any help appreciated
__
There are lots of string functions and operators:
http://www.postgresql.org/docs/9.3/static/functions-string.html
There is definitely a replacement for Oracle’s instr(…).
Regards,
Igor Neyman
Re: [GENERAL] Synchronous replication and read consistency
Ravi Krishna sravikrish...@gmail.com wrote: As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. No, it means that if the primary is hit by a meteor and you promote the standby, the data will not have been lost. The time between the successful return of the commit on the primary and the time at which the change becomes visible on the standby is normally quite small; you may have trouble running into a case where you notice it, but it can happen. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Not necessarily. There has been discussion of adding a new mode which will delay the commit on the primary until it is visible on a synchronous standby, but I don't recall where that left off. One of the issues is that with the current guarantee you need multiple replicas to prevent a failure of a standby from stalling the primary indefinitely, and you don't have an easy way to know *which* replica succeeded in persisting the transaction without doing a lot of work. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Synchronous replication and read consistency
Chris/Joshua I would like to know more details. As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Ah, sorry I misread sync replication as streaming replication... Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Hope it is clear. Synchronous replication is slower by nature. It will slow down the master as well because each commit has to wait for a standby to ack it. The answer to your question is still yes, you will get the same result on the standby. You will actually see less lag than with normal streaming replication in the sense that the standby lagging several transactions behind due to a commit/write burst on the master is not possible anymore. This is of course at the expense of master-performance. Bye, Chris. -- 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 upgrade from 8.4 to latest
Thanks for good suggestions. On Tue, Jul 28, 2015 at 3:13 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/28/2015 01:35 PM, AI Rumman wrote: But what I read, in-place upgrade has smaller outage, compared to dump/restore. Correct, in fact if you do it with the link option, it will be very fast. But so many articles on having bugs afterwards. Do you think it is a good idea to use pg_upgrade for critical database application? It entirely depends, I have successfully used pg_upgrade many, many times. That is what -c is for, to work out all the kinks before you upgrade. Or any other tool should I consider? For example - slony? On at 2.5TB database, you very well be doing a lot more harm than good using a tool such as slony. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you.
[GENERAL] xmin horizon?
All; The documentation for pg_stat_activity lists this column: backend_xmin xid The current backend's xmin horizon. Can someone point me to a better understanding on xmin horizon? Thanks in advance -- 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] Synchronous replication and read consistency
Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag. Basically yes. Of course there is *some* latency, at the very least from the network. If I run a process on a standby machine that displays a value every 0.1 sec and update the value on the master, I see the standby updating with a lag that feels less than 0.2 sec or so. You might have lag, however, in situations where you have so much write into the master that the network or standby is not able to catch up. After the write burst is over, the stanby will catch up as it quickly as possible, though. Also, you use the word consistency, that would be something else... Of course you always get consistent data, lag or not. This is Postgres after all :) Bye, Chris. -- 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] instr detail
Hi,
On Wed, 2015-07-29 at 22:03 +0530, Ramesh T wrote:
is instr available in postgres 9.3..?
in oracle instr('12.32.42','.',-1) ,any help appreciated
Orafce extension includes instr function:
https://github.com/orafce/orafce
Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR
--
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] Synchronous replication and read consistency
Chris/Joshua I would like to know more details. As per this: http://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION When requesting synchronous replication, each commit of a write transaction will wait until confirmation is received that the commit has been written to the transaction log on disk of both the primary and standby server. Does it mean that, on the standby, when PG writes the transaction log on the disk, it also updates the data buffers to make the transaction visible for all sessions. Eg: On the primary A big transaction committed Now if I issue a select on the primary looking for the transaction I committed above, I will get what I want. Will I get the same result if instead of primary I issue the select on the standby. Hope it is clear. On Wed, Jul 29, 2015 at 2:20 PM, Chris Mair ch...@1006.org wrote: Does sync replication guarantee that any inserted data on primary is immediately visible for read on standbys with no lag. Basically yes. Of course there is *some* latency, at the very least from the network. If I run a process on a standby machine that displays a value every 0.1 sec and update the value on the master, I see the standby updating with a lag that feels less than 0.2 sec or so. You might have lag, however, in situations where you have so much write into the master that the network or standby is not able to catch up. After the write burst is over, the stanby will catch up as it quickly as possible, though. Also, you use the word consistency, that would be something else... Of course you always get consistent data, lag or not. This is Postgres after all :) Bye, Chris. -- 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 horizon?
On Thu, Jul 30, 2015 at 4:13 AM, CS DBA cs_...@consistentstate.com wrote: The documentation for pg_stat_activity lists this column: backend_xmin xid The current backend's xmin horizon. Can someone point me to a better understanding on xmin horizon? This defines the oldest transaction ID that a given backend is currently seeing. -- Michael -- 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] Question about copy from with timestamp format
How do I specify that when I use copy from? this is what I am trying right
now..
copy myTable (myTimeCol, col2) from myFile delimiter as '\t'
I am not sure how to specify the time format..
thanks, murali.
On Wed, Jul 29, 2015 at 3:49 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 07/29/2015 03:42 PM, Murali M wrote:
Hi,
I wanted to copy a file from local file system to postgres. I have
timestamp value specified as:
MMDDHH24 format -- for example:
2015072913 -- is July 29, 2015 at 13:00
how do I import this data into a timestamp field?
thanks, murali.
PS: I believe if I need the hour, I need to use timestamp (I do not want
to put the hour as a separate column). I believe date datatype does not
work, if I am right??
test=# create table ts_test(ts_fld timestamp);
CREATE TABLE
test=# insert into ts_test values (to_timestamp('2015072913',
'MMDDHH24'));
INSERT 0 1
test=# select * from ts_test ;
ts_fld
-
2015-07-29 13:00:00
(1 row)
For more information:
http://www.postgresql.org/docs/9.4/interactive/functions-formatting.html
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: [GENERAL] instr detail
Melvin Davidson melvin6...@gmail.com writes: Based om the definition of Oracle instr(), the equivalent PostgreSQL function would be position(substring in string). See http://www.postgresql.org/docs/9.4/static/plpgsql-porting.html particularly the appendix at the bottom. I'm not sure that code is still the best way to do it (it's very old), but it's 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] xmin horizon?
On 29/07/15 21:13, CS DBA wrote: The documentation for pg_stat_activity lists this column: backend_xmin xid The current backend's xmin horizon. Can someone point me to a better understanding on xmin horizon? https://momjian.us/main/writings/pgsql/mvcc.pdf you can find this talk also on youtube. It's worth watching. Torsten -- 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] Question about copy from with timestamp format
On 07/29/2015 03:42 PM, Murali M wrote:
Hi,
I wanted to copy a file from local file system to postgres. I have
timestamp value specified as:
MMDDHH24 format -- for example:
2015072913 -- is July 29, 2015 at 13:00
how do I import this data into a timestamp field?
thanks, murali.
PS: I believe if I need the hour, I need to use timestamp (I do not want
to put the hour as a separate column). I believe date datatype does not
work, if I am right??
test=# create table ts_test(ts_fld timestamp);
CREATE TABLE
test=# insert into ts_test values (to_timestamp('2015072913',
'MMDDHH24'));
INSERT 0 1
test=# select * from ts_test ;
ts_fld
-
2015-07-29 13:00:00
(1 row)
For more information:
http://www.postgresql.org/docs/9.4/interactive/functions-formatting.html
--
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] Question about copy from with timestamp format
On 07/29/2015 03:55 PM, Murali M wrote:
How do I specify that when I use copy from? this is what I am trying
right now..
copy myTable (myTimeCol, col2) from myFile delimiter as '\t'
Argh, missed that.
I am not sure how to specify the time format..
Yeah, the time component prevents you from even changing the datestyle
to get the data in. Looks you are going to have either change the values
before you do the COPY or do the COPY to a temporary/staging table and
then do the to_timestamp when you transfer to the final table.
thanks, murali.
On Wed, Jul 29, 2015 at 3:49 PM, Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote:
On 07/29/2015 03:42 PM, Murali M wrote:
Hi,
I wanted to copy a file from local file system to postgres. I have
timestamp value specified as:
MMDDHH24 format -- for example:
2015072913 tel:2015072913 -- is July 29, 2015 at 13:00
how do I import this data into a timestamp field?
thanks, murali.
PS: I believe if I need the hour, I need to use timestamp (I do
not want
to put the hour as a separate column). I believe date datatype
does not
work, if I am right??
test=# create table ts_test(ts_fld timestamp);
CREATE TABLE
test=# insert into ts_test values (to_timestamp('2015072913
tel:2015072913', 'MMDDHH24'));
INSERT 0 1
test=# select * from ts_test ;
ts_fld
-
2015-07-29 13:00:00
(1 row)
For more information:
http://www.postgresql.org/docs/9.4/interactive/functions-formatting.html
--
Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com
--
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] Question about copy from with timestamp format
Based on your PS asking about data types and commenting that you don't want to put hour in a separate column, it sounds like this is a brand-new table you're creating. If so, and if this is a one-time COPY operation, you can create a text column for the initial import. Then after you're done importing, you can execute ALTER TABLE ts_test ALTER COLUMN ts_fld TYPE TIMESTAMP USING (to_timestamp(ts_fld, 'MMDDHH24')); to convert the format of the imported data to a timestamp. Then you're set. If there will be ongoing imports of more files like this, though, you'll need the intermediate table solution offered by Adrian. I was going to suggest a trigger, but it turns out that the data type checking happens even before the BEFORE trigger fires, so you don't get a chance to massage your data before actually inserting it. I got 'ERROR: date/time field value out of range: 2015072913' before the trigger even fired. I wonder if that's deliberate? I was able to implement a workaround by adding a raw_ts_fld column of type text, but an extra column might be too ugly for you relative to a temp table, I don't know. Sherrylyn P.S. Yes, you're right that the date data type won't work if you want to keep the hour value in the same column. On Wed, Jul 29, 2015 at 7:47 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/29/2015 03:55 PM, Murali M wrote: How do I specify that when I use copy from? this is what I am trying right now.. copy myTable (myTimeCol, col2) from myFile delimiter as '\t' I am not sure how to specify the time format.. My previous post would have been more useful if I had added that the temporary/staging table should have the 'timestamp' field set to varchar/text so you could get the data in. thanks, murali. -- 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] Question about timelines
On Wed, Jul 29, 2015 at 3:46 PM, Torsten Förtsch torsten.foert...@gmx.net wrote: Hi, we have a complex structure of streaming replication (PG 9.3) like: master -- replica1 | +- replica2 -- replica21 | +-- replica22 -- replica221 Now I want to retire master and make replica2 the new master: +-- replica1 | replica2 -- replica21 | +-- replica22 -- replica221 replica2 is currently a synchronous replica. If I promote replica2 a new timeline is created. Hence, I have to instruct all other replicas to follow that new timeline (recovery_target_timeline = 'latest' in recovery.conf). PostgreSQL 9.3 supports cascade standby to follow automatically the new master after the timeline switch. In your case even if the timeline is changed, you need to start the standby setup for replica1 only from scratch. All others follows automatically the new master. Regards, Hari Babu Fujitsu Australia -- 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] Lots of stuck queries after upgrade to 9.4
On 07/28/2015 11:36 PM, Heikki Linnakangas wrote:
A-ha, I succeeded to reproduce this now on my laptop, with pgbench! It
seems to be important to have a very large number of connections:
pgbench -n -c400 -j4 -T600 -P5
That got stuck after a few minutes. I'm using commit_delay=100.
Now that I have something to work with, I'll investigate this more tomorrow.
Ok, it seems that this is caused by the same issue that I found with my
synthetic test case, after all. It is possible to get a lockup because
of it.
For the archives, here's a hopefully easier-to-understand explanation of
how the lockup happens. It involves three backends. A and C are
insertion WAL records, while B is flushing the WAL with commit_delay.
The byte positions 2000, 2100, 2200, and 2300 are offsets within a WAL
page. 2000 points to the beginning of the page, while the others are
later positions on the same page. WaitToFinish() is an abbreviation for
WaitXLogInsertionsToFinish(). Update pos X means a call to
WALInsertLockUpdateInsertingAt(X). Reserve A-B means a call to
ReserveXLogInsertLocation, which returned StartPos A and EndPos B.
Backend A Backend B Backend C
- - -
Acquire InsertLock 2
Reserve 2100-2200
Calls WaitToFinish()
reservedUpto is 2200
sees that Lock 1 is
free
Acquire InsertLock 1
Reserve 2200-2300
GetXLogBuffer(2200)
page not in cache
Update pos 2000
AdvanceXLInsertBuffer()
run until about to
acquire WALWriteLock
GetXLogBuffer(2100)
page not in cache
Update pos 2000
AdvanceXLInsertBuffer()
Acquire WALWriteLock
write out old page
initialize new page
Release WALWriteLock
finishes insertion
release InsertLock 2
WaitToFinish() continues
sees that lock 2 is
free. Returns 2200.
Acquire WALWriteLock
Call WaitToFinish(2200)
blocks on Lock 1,
whose initializedUpto
is 2000.
At this point, there is a deadlock between B and C. B is waiting for C
to release the lock or update its insertingAt value past 2200, while C
is waiting for WALInsertLock, held by B.
To fix that, let's fix GetXLogBuffer() to always advertise the exact
position, not the beginning of the page (except when inserting the first
record on the page, just after the page header, see comments).
This fixes the problem for me. I've been running pgbench for about 30
minutes without lockups now, while without the patch it locked up within
a couple of minutes. Spiros, can you easily test this patch in your
environment? Would be nice to get a confirmation that this fixes the
problem for you too.
- Heikki
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 8e9754c..307a04c 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1839,11 +1839,32 @@ GetXLogBuffer(XLogRecPtr ptr)
endptr = XLogCtl-xlblocks[idx];
if (expectedEndPtr != endptr)
{
+ XLogRecPtr initializedUpto;
+
/*
- * Let others know that we're finished inserting the record up to the
- * page boundary.
+ * Before calling AdvanceXLInsertBuffer(), which can block, let others
+ * know how far we're finished with inserting the record.
+ *
+ * NB: If 'ptr' points to just after the page header, advertise a
+ * position at the beginning of the page rather than 'ptr' itself. If
+ * there are no other insertions running, someone might try to flush
+ * up to our advertised location. If we advertised a position after
+ * the page header, someone might try to flush the page header, even
+ * though page might actually not be initialized yet. As the first
+ * inserter on the page, we are effectively responsible for making
+ * sure that it's initialized, before we let insertingAt to move past
+ * the page header.
*/
- WALInsertLockUpdateInsertingAt(expectedEndPtr - XLOG_BLCKSZ);
+ if (ptr % XLOG_BLCKSZ == SizeOfXLogShortPHD
+ ptr % XLOG_SEG_SIZE XLOG_BLCKSZ)
+ initializedUpto = ptr - SizeOfXLogShortPHD;
+ else if (ptr % XLOG_BLCKSZ == SizeOfXLogLongPHD
+ ptr % XLOG_SEG_SIZE XLOG_BLCKSZ)
+ initializedUpto = ptr - SizeOfXLogLongPHD;
+ else
+ initializedUpto = ptr;
+
+ WALInsertLockUpdateInsertingAt(initializedUpto);
AdvanceXLInsertBuffer(ptr, false);
endptr = XLogCtl-xlblocks[idx];
--
Sent
Re: [GENERAL] Question about copy from with timestamp format
On 07/29/2015 03:55 PM, Murali M wrote: How do I specify that when I use copy from? this is what I am trying right now.. copy myTable (myTimeCol, col2) from myFile delimiter as '\t' I am not sure how to specify the time format.. My previous post would have been more useful if I had added that the temporary/staging table should have the 'timestamp' field set to varchar/text so you could get the data in. thanks, murali. -- 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] conn = PQconnectdb(conninfo);
I've found perhaps a bug.
I've narrowed down my code and the problem is indeed at: conn =
PQconnectdb(conninfo);
My connection string: host=192.168.178.12 dbname=DATABASE user=foo
password=bar
When I remove key/value host=xxx then everything is OK. Valgrind mentions:
no leaks are possible.
When key/value host=xxx is added, not everything is freed and there are
tons of bytes still reachable.
==9195==
==9195== HEAP SUMMARY:
==9195== in use at exit: 450,080 bytes in 2,829 blocks
==9195== total heap usage: 9,476 allocs, 6,647 frees, 7,810,733 bytes
allocated
==9195==
==9195== LEAK SUMMARY:
==9195==definitely lost: 0 bytes in 0 blocks
==9195==indirectly lost: 0 bytes in 0 blocks
==9195== possibly lost: 0 bytes in 0 blocks
==9195==still reachable: 450,080 bytes in 2,829 blocks
==9195== suppressed: 0 bytes in 0 blocks
==9195== Rerun with --leak-check=full to see details of leaked memory
==9195==
==9195== For counts of detected and suppressed errors, rerun with: -v
==9195== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 14 from 6)
The network address is the machine's address where I was testing on. So I
could also have used localhost or 127.0.0.1 but this gave me the same
result when using the network address.
Played with hostaddr as well and gave me the same result.
http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PQCONNECTDB
http://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
The attachment is the program I've used for testing.
2015-07-18 0:15 GMT+02:00 Tom Lane t...@sss.pgh.pa.us:
Peter Kroon plakr...@gmail.com writes:
Every now and then my program will abort.
IAnd this is because: conn = PQconnectdb(conninfo);
The error given:
*** Error in `./server_prog': malloc(): smallbin double linked list
corrupted: 0x092c10a0 ***
This looks like malloc() complaining because something has corrupted its
bookkeeping data, which generally means that something previously wrote
past the end of a malloc'd data chunk, or tried to write into an
already-freed chunk. The odds are very high that the bug is in your
program rather than libpq, though, because no such problems have been
found within libpq recently.
valgrind is a fairly useful tool for tracking down such issues.
regards, tom lane
#include stdio.h
#include stdlib.h
#include pthread.h
#include /usr/include/postgresql/libpq-fe.h
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
void *print_message_function( void *ptr );
main()
{
int MAX = 10;
pthread_t thread[MAX];
const char *message = Hello Wordl!;
int iret[MAX];
/* Create independent threads each of which will execute function */
int i;
for ( i = 0; i MAX; i++ )
{
iret[i] = pthread_create( thread[i], NULL, print_message_function, (void*) message);
if(iret[i])
{
fprintf(stderr,Error, return code: %d\n, iret[i]);
exit(EXIT_FAILURE);
}
}
for ( i = 0; i MAX; i++ )
printf(pthread_create(), thread %d returns: %d\n, i, iret[i]);
for ( i = 0; i MAX; i++ )
pthread_join( thread[i], NULL);
exit(EXIT_SUCCESS);
}
void *print_message_function( void *ptr )
{
char *message;
message = (char *) ptr;
printf(%s \n, message);
// database variable
const char *conninfo;
PGconn *conn;
PGresult *res;
int nFields,
nRows;
int i,
j;
int result_ok = 0;
conninfo = host=127.0.0.1 dbname=postgres user=postgres password=XXX port=5432;
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, Connection to database failed: %s,
PQerrorMessage(conn));
exit_nicely(conn);
}
/* Start a transaction block */
res = PQexec(conn, BEGIN);
PQclear(res);
/* disable notice */
res = PQexec(conn, SET client_min_messages TO NOTICE;);
PQclear(res);
/* end the transaction */
res = PQexec(conn, END);
PQclear(res);
/* close the connection to the database and clean-up */
PQfinish(conn);
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
