Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

[Albe Laurenz]

Anoop K wrote:
> We are hitting a situation where REINDEX is resulting in postgresql to go to 
> dead lock state for ever.
> On debugging the issue we found that
> 3 connections are going in to some dead lock state.
> 
> 1.idle in transaction
> 2.REINDEX waiting
> 3.SELECT waiting
> 
> All these connections are made in the same minute. Once in deadlock state we 
> are not able to make new
> connections to db.(So not able to view pg_locks also). New connections 
> appears as 'startup waiting' in
> ps output. Initially we suspected  is the result of not 
> closing a connection. But
> it seems it got stuck after creating a connection and is not able to proceed.
> 
> Any clues ..

Check the contents of pg_locks:
What locks does the "idle in transaction" session hold?
Who holds the locks that block SELECT, REINDEX and new connections?

Turn on log_statement='all' to see what the "idle in transaction"
session did since it started.

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] ERROR: invalid input syntax for integer: ""

[Ben Madin]

Thank you to all for your help on this problem. I've summarised the resolution 
in the hope that it might help someone else.

With all the advice I have gone forward and discovered that the issue related 
to a postcode anomaly. A client had provided a new postbox postcode (the 
application normally prevents this for postboxes because we can't locate 
properties, but because it was new - and our database didn't have a record of 
it - this check had been bypassed). This meant there was no geometry associated 
with the postcode, and when it was joined to the postcodes table (which has 
varchars for postcodes because in Australia some postcodes begin with 0, which 
needs to be printed to allow automatic sorting) during the distance checking 
function (which looked like this in pl/pgsql):

round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000)

If a geometry is NULL, the st_distance_sphere postgis function returned NULL. 

NULL/1000 = NULL

round(NULL) = NULL

AND NULL < 150 = NULL

so the predicate probably looks like:

AND round(NULL/1000) < 150

AND NULL, so no row returned.

This can't be used in a comparison, so to get around this (thanks Tom) :

coalesce(round(st_distance_sphere( '$$ || pccentre || $$', 
post.the_point)/1000),0) < $$ || quote_literal(distance);

which works - problem no longer being seen.

My final throught relates to the message:

ERROR:  invalid input syntax for integer: ''

The '' suggests (I don't think I was the only one who thought this) that we 
were looking for a string comparison. I guess the NULL value is in there 
between the quotes.

cheers

Ben








On 2013-02-07, at 00:01 , Tom Lane  wrote:

> Ben Madin  writes:
>> On 2013-02-06, at 13:42 , Tom Lane  wrote:
>>> The only part of this query that looks like it could possibly produce
>>> that error is the res8.resultvalue-to-int cast:
> 
 LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 
 108 AND res8.del = false
 LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = 
 res8.resultvalue::int
>>> 
>>> ^
>>> Presumably, there are some empty strings in results.resultvalue, and if
>>> the query happens to try to compare one of them to rlu8.id, kaboom.
> 
>> Yes - this would be the case if it tried to match it against the resultvalue 
>> only - some of the values in the table are NULL, but not for this 
>> resulttypeid.
> 
> NULLs are not the problem (casting a NULL to anything is still a NULL).
> The problem you've got is with empty strings, which are not at all the
> same thing, even if Oracle can't tell the difference.
> 
>> So my understanding, working left to right was that the res.8 table rows 
>> should be limited to those rows which have a resulttypeid = 108.
> 
> Please recall the section in the fine manual where it points out that
> WHERE clauses are not evaluated left-to-right.  In the case at hand
> I think the planner may be able to rearrange the join order, such that
> the rlu8 join is done first.  Now, having said that, I'm not real sure
> why the res8.resulttypeid = 108 clause couldn't be applied at scan level
> not join level.  But you really need to be looking at EXPLAIN output
> rather than theorizing about what order the clauses will be checked in.
> 
>> I'm really not sure what to do here.
> 
> You need to make sure the join clause is safe to evaluate for any data
> present in the table.  The first question I'd ask is why isn't
> resultvalue of a numeric type to start with --- this whole problem
> smells of crummy schema design.  Or at least, why can't you use NULL
> for the offending values instead of empty strings.  If you really can't
> fix the data representation, you need to complicate the join clause to
> make it not try to convert non-integral strings to ints.  One possible
> solution is "nullif(res8.resultvalue, '')::int", if empty strings are
> the only hazard.  If they're not, you could do something with a CASE
> expression using a regex test on the string...
> 
>   regards, tom lane


-- 

Ben Madin

t : +61 8 6102 5535
m : +61 448 887 220
e : b...@ausvet.com.au

AusVet Animal Health Services
P.O. Box 5467
Broome   WA   6725
Australia

AusVet's website:  http://www.ausvet.com.au

This transmission is for the intended addressee only and is confidential 
information. If you have received this transmission in error, please delete it 
and notify the sender. The contents of this email are the opinion of the writer 
only and are not endorsed by AusVet Animal Health Services unless expressly 
stated otherwise. Although AusVet uses virus scanning software we do not accept 
liability for viruses or similar in any attachments. Thanks for reading.



-- 
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] DEFERRABLE NOT NULL constraint

[Albe Laurenz]

Gavan Schneider wrote:
> Taking a different tangent ...

Good idea.

> Is there anything in the SQL standards about NOT NULL
> constraints being deferrable?
> 
> To my mind we should not consider implementing non-standard
> behaviour, but if something is in the standard I can't see why
> it shouldn't be implemented, esp. when there is no compulsion
> for it to be used.

ISO/IEC 9075-2:2003 says:

Chapter 11.4 ():

 ::=
  [  ]  [  ]

 ::=
NOT NULL
  | 
  | 
  | 

Chapter 10.8 ( and ):

 ::=
 [ [ NOT ] DEFERRABLE ]
  | [ NOT ] DEFERRABLE [  ]

 ::=
INITIALLY DEFERRED
  | INITIALLY IMMEDIATE


So yes, the standard caters for deferrable NOT NULL constraints.

Moreover:

Chapter 10.8, General Rules
1) A  identifies a constraint. Let the identified constraint 
be C.
2) If NOT DEFERRABLE is specified, then C is not deferrable; otherwise it is 
deferrable.

So deferrable should be the default.

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

[GENERAL] Re: [HACKERS] function for setting/getting same timestamp during whole transaction

[Miroslav Šimulčík]

Nice. This solves problem with clearing of session variables. Thank you

Miro


2013/2/7 Gurjeet Singh 

> Removing -hackers and adding -general
>
>
> On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík  > wrote:
>
>>
>>
>>
>>>  Alternately, you might be able to use a custom GUC from a rather
>>> smaller PL/PgSQL function. At transaction start, issue:
>>>
>>> set_config('myapp.trigger_time', '', 't');
>>>
>>
>> This is problem with using custom GUC - clearing variable at transaction
>> start. Without clearing it's not sufficient solution (see my response to
>> Pavel's mail). I don't want to do clearing from application and as far as i
>> know there is not "transaction start" trigger.
>>
>
> I think you can do it by using 2 GUCs.
>
> Put this at the beginning of your trigger:
>
> if current_setting(my_var.now) != now() then
> -- We're executing this code for the first time in this transaction
> set_config(my_var.now, now())
> set_config(my_var.my_ts, clock_timestamp)
> end;
> -- Go on happily use my_var.my_ts to stamp your data.
>
> HTH,
> --
> Gurjeet Singh
>
> http://gurjeet.singh.im/
>
>

[GENERAL] REINDEX deadlock - Postgresql -9.1

[Anoop K]

We are hitting a situation where REINDEX is resulting in postgresql to go
to dead lock state* for ever*. On debugging the issue we found that
3 connections are going in to some dead lock state.

   1. *idle in transaction   *
   2. *REINDEX waiting   *
   3. *SELECT waiting*

All these connections are made in the same minute. Once in deadlock state
we are not able to make new connections to db.(So not able to view pg_locks
also). New connections appears as *'startup waiting*' in *ps* output.
Initially we suspected ** is the result of not closing
a connection. But it seems it got stuck after creating a connection and is
not able to proceed.

Any clues ..

Thanks
Anoop

Re: [GENERAL] Hot Standby has PANIC: WAL contains references to invalid pages

[amutu]

maybe pg_basebackup can`t handle such big database.try
rsync,pg_start_backup,rsync,pg_stop_backup,it always works fine for us.our
instance is about 2TB and we use pg9.1.x.

jov
在 2013-2-7 下午2:25，"Michael Harris" 写道：

> Hi Hari,
>
> Thanks for the tip. We tried applying that patch, however the error
> recurred exactly as before.
>
> Regards // Mike
>
>
> -Original Message-
> From: Hari Babu [mailto:haribabu.ko...@huawei.com]
> Sent: Tuesday, 5 February 2013 10:07 PM
> To: Michael Harris; pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Hot Standby has PANIC: WAL contains references to
> invalid pages
>
> On Tuesday, February 05, 2013 6:05 AM Michael Harris wrote:
> >Any ideas for a next troubleshooting step?
>
> [BUG?] lag of minRecoveryPont in archive recovery, which has fixed
> recently.
> Please check the following link for more details. It may help.
>
>
> http://www.postgresql.org/message-id/20121206.130458.170549097.horiguchi.kyo
> t...@lab.ntt.co.jp
>
> Regards,
> Hari babu.
>
>
>
>
>
> --
> 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] Hot Standby has PANIC: WAL contains references to invalid pages

[Michael Harris]

Hi Hari,

Thanks for the tip. We tried applying that patch, however the error recurred 
exactly as before.

Regards // Mike


-Original Message-
From: Hari Babu [mailto:haribabu.ko...@huawei.com] 
Sent: Tuesday, 5 February 2013 10:07 PM
To: Michael Harris; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Hot Standby has PANIC: WAL contains references to 
invalid pages

On Tuesday, February 05, 2013 6:05 AM Michael Harris wrote:
>Any ideas for a next troubleshooting step?

[BUG?] lag of minRecoveryPont in archive recovery, which has fixed recently.
Please check the following link for more details. It may help.

http://www.postgresql.org/message-id/20121206.130458.170549097.horiguchi.kyo
t...@lab.ntt.co.jp

Regards,
Hari babu.





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

[GENERAL] Re: [HACKERS] function for setting/getting same timestamp during whole transaction

[Gurjeet Singh]

Removing -hackers and adding -general

On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík
wrote:

>
>
>
>>  Alternately, you might be able to use a custom GUC from a rather smaller
>> PL/PgSQL function. At transaction start, issue:
>>
>> set_config('myapp.trigger_time', '', 't');
>>
>
> This is problem with using custom GUC - clearing variable at transaction
> start. Without clearing it's not sufficient solution (see my response to
> Pavel's mail). I don't want to do clearing from application and as far as i
> know there is not "transaction start" trigger.
>

I think you can do it by using 2 GUCs.

Put this at the beginning of your trigger:

if current_setting(my_var.now) != now() then
-- We're executing this code for the first time in this transaction
set_config(my_var.now, now())
set_config(my_var.my_ts, clock_timestamp)
end;
-- Go on happily use my_var.my_ts to stamp your data.

HTH,
-- 
Gurjeet Singh

http://gurjeet.singh.im/

Re: [GENERAL] configuring timezone

[Igor Neyman]

Thank you for explaining.

Regards,
Igor Neyman


From: Tom Lane [t...@sss.pgh.pa.us]
Sent: Wednesday, February 06, 2013 5:11 PM
To: Igor Neyman
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

Igor Neyman  writes:
> Going back to the reason for this change in Release Notes:
> "This avoids expensive time zone probes during server start."
> How expensive?

The time zone probe logic involves reading every file under
/usr/share/zoneinfo (or wherever you have the Olson tz database
installed).  There are a couple thousand of those in a typical Linux
installation.  In a cold-boot situation where none of that data is
already swapped in, it's not unusual for this to take five seconds or
more.  Now that may or may not seem like a lot, but it's more than
enough to cause many startup scripts to conclude that the postmaster has
failed.  The hacks we'd built up to deal with this eventually became
insupportable.

We're not going back.  I suggest you consider ways to adjust your
server-migration process.

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] configuring timezone

[Terence Ferraro]

9.2.1 was the version standard when I was building and deploying...so no, I
probably will not (personally) be updating anytime soon...

However, if you're interested, I'll see if I can find a place tonight or
tomorrow to put these binaries (they are 32-bit as well), source, etc
(sourceforge maybe?). I can also include to inno setup script that builds
an installer similar to the EnterpriseDB version; that is, it automatices
the service setup, creates a postgres user, etc. Hell, I may as well
include the pre-built installer, too, if you don't want to customize
anything..

In addition to the timezone fix, I (originally) wanted to build my own
Windows installer because the EnterpriseDB version does NOT link against
zlib with respect to openssl. In other words, no compressed ssl connections
are possible with the currently distributed windows version. This one is
linked against zlib (and the speed increase is quite significant).

T.J.

On Wed, Feb 6, 2013 at 3:23 PM, Igor Neyman  wrote:

>  I am on Windows (both 32 and 64 bit)  using 32-bit Postgres.
>
> So, your binaries are for 9.2.1, you aren’t planning to go to 9.2.2?
>
> ** **
>
> ** **
>
> *From:* Terence Ferraro [mailto:terencejferr...@gmail.com]
> *Sent:* Wednesday, February 06, 2013 3:07 PM
> *To:* Igor Neyman
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] configuring timezone
>
> ** **
>
> Sorry, but from what I understand the change is permanent. If recompile is
> not an option but you're on Windows let me know; I do have binaries
> available..
>
> On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman 
> wrote:
>
> Terence,
>
>  
>
> Thanks for quick reply, I read your thread (Dec, 2012) before posting my
> question.
>
> But, recompile is not an option for me.  Was hoping, that something
> regarding this issue changed since…
>
>  
>
> Igor Neyman
>
>  
>
> *From:* Terence Ferraro [mailto:terencejferr...@gmail.com]
> *Sent:* Wednesday, February 06, 2013 1:45 PM
> *To:* Igor Neyman
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] configuring timezone
>
>  
>
> See the archived thread here:
> http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=q...@mail.gmail.com
>
> Short version: Sorry, but you're going to need to recompile if you want
> that behavior. Here's a diff applied against 9.2.1
> http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple
> dozen times now and it is working flawlessly.
>
> T.J.
>
> On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman 
> wrote:
>
> Timezone configuration parameter (defaulting to system timezone)  worked
> fine for us before upgrading from 8.4. to 9.2.
>
>  
>
> Now we’ve got a problem. 
>
> 9.2 Release Notes says:
>
> ·  Identify the server time zone during initdb, and set postgresql.confentries
> timezoneand
> log_timezoneaccordingly
>  (Tom Lane)
> 
>
> This avoids expensive time zone probes during server start.
>
>  
>
> Question: is there any way to revert back to old behavior so that server
> will probe system’s timezone on startup (default to OS timezone on startup)
> instead setting it during initdb?
>
> Obviously, without recompiling/rebuilding Postgres.
>
>  
>
> I’m dealing with the situation, where system is being built in one
> timezone (could be anywhere around the globe), and then moved to other (not
> known during system build) location with different timezone.  
>
> After relocation, OS timezone will change, but we can’t allow user to edit
> timezone parameter in  Postgresql.conf.
>
>  
>
> Regards,
>
> Igor Neyman
>
>  
>
> ** **
>

Re: [GENERAL] configuring timezone

[Tom Lane]

Igor Neyman  writes:
> Going back to the reason for this change in Release Notes:
> "This avoids expensive time zone probes during server start."
> How expensive?

The time zone probe logic involves reading every file under
/usr/share/zoneinfo (or wherever you have the Olson tz database
installed).  There are a couple thousand of those in a typical Linux
installation.  In a cold-boot situation where none of that data is
already swapped in, it's not unusual for this to take five seconds or
more.  Now that may or may not seem like a lot, but it's more than
enough to cause many startup scripts to conclude that the postmaster has
failed.  The hacks we'd built up to deal with this eventually became
insupportable.

We're not going back.  I suggest you consider ways to adjust your
server-migration process.

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] configuring timezone

[Adrian Klaver]

On 02/06/2013 01:47 PM, Igor Neyman wrote:
> 

>>
>> --
>> Adrian Klaver
>> adrian.kla...@gmail.com
> 
> Sometimes, but not always.

I guess you could ship a script that sets the timezone 
when the server is installed.

> 
> Going back to the reason for this change in Release Notes:
> 
> "This avoids expensive time zone probes during server start."
> 
> How expensive? How often Postgres is restarted?
> We aren't restarting Postgres for months.  Doesn't seem to be very valid 
> reason, at least not for us :)

Here is the -hackers message that relates;

http://www.postgresql.org/message-id/e1r296v-00014a...@gemulon.postgresql.org

> 
> Igor Neyman
> 
> 


-- 
Adrian Klaver
adrian.kla...@gmail.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] configuring timezone

[Igor Neyman]

> -Original Message-
> From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
> Sent: Wednesday, February 06, 2013 4:40 PM
> To: Igor Neyman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] configuring timezone
> 
> On 02/06/2013 10:32 AM, Igor Neyman wrote:
> > Timezone configuration parameter (defaulting to system timezone)
> > worked fine for us before upgrading from 8.4. to 9.2.
> >
> > Now we've got a problem.
> >
> > 9.2 Release Notes says:
> >
> > *  Identify the server time zone during initdb, and set
> > postgresql.conf entries timezone
> >  client.html#
> > GUC-TIMEZONE>
> > and log_timezone
> >  logging.html
> > #GUC-LOG-TIMEZONE>
> > accordingly (Tom Lane)
> >
> > This avoids expensive time zone probes during server start.
> >
> > Question: is there any way to revert back to old behavior so that
> > server will probe system's timezone on startup (default to OS
> timezone
> > on
> > startup) instead setting it during initdb?
> >
> > Obviously, without recompiling/rebuilding Postgres.
> >
> > I'm dealing with the situation, where system is being built in one
> > timezone (could be anywhere around the globe), and then moved to
> other
> > (not known during system build) location with different timezone.
> >
> > After relocation, OS timezone will change, but we can't allow user to
> > edit timezone parameter in  Postgresql.conf.
> 
> 
> It is not possible to change the postgresql.conf just before the
> relocate? In other words do you have no idea where the server will end
> up?
> 
> >
> > Regards,
> >
> > Igor Neyman
> >
> 
> 
> --
> Adrian Klaver
> adrian.kla...@gmail.com

Sometimes, but not always.

Going back to the reason for this change in Release Notes:

"This avoids expensive time zone probes during server start."

How expensive? How often Postgres is restarted?
We aren't restarting Postgres for months.  Doesn't seem to be very valid 
reason, at least not for us :)

Igor Neyman




-- 
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] configuring timezone

[Adrian Klaver]

On 02/06/2013 10:32 AM, Igor Neyman wrote:

Timezone configuration parameter (defaulting to system timezone)  worked
fine for us before upgrading from 8.4. to 9.2.

Now we’ve got a problem.

9.2 Release Notes says:

·  Identify the server time zone during initdb, and set postgresql.conf
entries timezone

and log_timezone

accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server
will probe system’s timezone on startup (default to OS timezone on
startup) instead setting it during initdb?

Obviously, without recompiling/rebuilding Postgres.

I’m dealing with the situation, where system is being built in one
timezone (could be anywhere around the globe), and then moved to other
(not known during system build) location with different timezone.

After relocation, OS timezone will change, but we can’t allow user to
edit timezone parameter in  Postgresql.conf.



It is not possible to change the postgresql.conf just before the 
relocate? In other words do you have no idea where the server will end up?




Regards,

Igor Neyman




--
Adrian Klaver
adrian.kla...@gmail.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] configuring timezone

[Igor Neyman]

I am on Windows (both 32 and 64 bit)  using 32-bit Postgres.
So, your binaries are for 9.2.1, you aren't planning to go to 9.2.2?


From: Terence Ferraro [mailto:terencejferr...@gmail.com]
Sent: Wednesday, February 06, 2013 3:07 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

Sorry, but from what I understand the change is permanent. If recompile is not 
an option but you're on Windows let me know; I do have binaries available..
On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman 
mailto:iney...@perceptron.com>> wrote:
Terence,

Thanks for quick reply, I read your thread (Dec, 2012) before posting my 
question.
But, recompile is not an option for me.  Was hoping, that something regarding 
this issue changed since...

Igor Neyman

From: Terence Ferraro 
[mailto:terencejferr...@gmail.com]
Sent: Wednesday, February 06, 2013 1:45 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

See the archived thread here: 
http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=q...@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want that 
behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2RF. 
I've deployed the patched version a couple dozen times now and it is working 
flawlessly.

T.J.
On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman 
mailto:iney...@perceptron.com>> wrote:
Timezone configuration parameter (defaulting to system timezone)  worked fine 
for us before upgrading from 8.4. to 9.2.

Now we've got a problem.
9.2 Release Notes says:

*  Identify the server time zone during initdb, and set postgresql.conf entries 
timezone
 and 
log_timezone
 accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server will 
probe system's timezone on startup (default to OS timezone on startup) instead 
setting it during initdb?
Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one timezone 
(could be anywhere around the globe), and then moved to other (not known during 
system build) location with different timezone.
After relocation, OS timezone will change, but we can't allow user to edit 
timezone parameter in  Postgresql.conf.

Regards,
Igor Neyman

Re: [GENERAL] configuring timezone

[Terence Ferraro]

Sorry, but from what I understand the change is permanent. If recompile is
not an option but you're on Windows let me know; I do have binaries
available..

On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman  wrote:

>  Terence,
>
> ** **
>
> Thanks for quick reply, I read your thread (Dec, 2012) before posting my
> question.
>
> But, recompile is not an option for me.  Was hoping, that something
> regarding this issue changed since…
>
> ** **
>
> Igor Neyman
>
> ** **
>
> *From:* Terence Ferraro [mailto:terencejferr...@gmail.com]
> *Sent:* Wednesday, February 06, 2013 1:45 PM
> *To:* Igor Neyman
> *Cc:* pgsql-general@postgresql.org
> *Subject:* Re: [GENERAL] configuring timezone
>
> ** **
>
> See the archived thread here:
> http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=q...@mail.gmail.com
>
> Short version: Sorry, but you're going to need to recompile if you want
> that behavior. Here's a diff applied against 9.2.1
> http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple
> dozen times now and it is working flawlessly.
>
> T.J.
>
> On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman 
> wrote:
>
> Timezone configuration parameter (defaulting to system timezone)  worked
> fine for us before upgrading from 8.4. to 9.2.
>
>  
>
> Now we’ve got a problem. 
>
> 9.2 Release Notes says:
>
> ·  Identify the server time zone during initdb, and set postgresql.confentries
> timezoneand
> log_timezoneaccordingly
>  (Tom Lane)
> 
>
> This avoids expensive time zone probes during server start.
>
>  
>
> Question: is there any way to revert back to old behavior so that server
> will probe system’s timezone on startup (default to OS timezone on startup)
> instead setting it during initdb?
>
> Obviously, without recompiling/rebuilding Postgres.
>
>  
>
> I’m dealing with the situation, where system is being built in one
> timezone (could be anywhere around the globe), and then moved to other (not
> known during system build) location with different timezone.  
>
> After relocation, OS timezone will change, but we can’t allow user to edit
> timezone parameter in  Postgresql.conf.
>
>  
>
> Regards,
>
> Igor Neyman
>
> ** **
>

Re: [GENERAL] Keeping historical record changes w/ triggers - best practices?

[Jeff Janes]

On Wed, Feb 6, 2013 at 11:19 AM, Wells Oliver  wrote:
> I don't mean to hog my own thread, but the more I look at the hstore type,
> the more reasonable it seems. The table is just a serial, a timestamp, and
> two columns 'old' and 'new'. The trigger function inserts these values using
> hstore(OLD) and hstore(NEW).
>
> Then, you can select old, new, and new - old, which returns an hstore of
> what changed, or you could store this as a third column called 'delta' or
> whatever. The hstore of course can be cast to a record, or any other
> suitable object.
>
> Of course, you could not store old and new, and only the delta if you
> preferred, but it's nice to have both records.
>
> Will anyone tell me there's some terrible side effect of this approach that
> I am not realizing?

If you want hstore-based history logs, you should read through this,
either to implement it, or for ideas on how to do your own:

http://wiki.postgresql.org/wiki/Audit_trigger_91plus


Cheers,

Jeff


-- 
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] DEFERRABLE NOT NULL constraint

[Gavan Schneider]

On Wednesday, February 6, 2013 at 23:31, 
00jkxma...@sneakemail.com (Alban Hertroys haramrae-at-gmail.com 
|pg-gts/Basic|) wrote:



On 6 February 2013 12:56, Chris Angelico  wrote:


If you get into a taxi and ask
to be driven to New Zealand within the hour, no amount of begging will
get you what you want.



Unless you get into a taxi in New Zealand.



  Which makes the request effectively NULL, planning to do 
this makes it DEFFERABLE.



Taking a different tangent ...

Is there anything in the SQL standards about NOT NULL 
constraints being deferrable?


To my mind we should not consider implementing non-standard 
behaviour, but if something is in the standard I can't see why 
it shouldn't be implemented, esp. when there is no compulsion 
for it to be used.


Regards
Gavan Schneider



--
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] Keeping historical record changes w/ triggers - best practices?

[Wells Oliver]

I don't mean to hog my own thread, but the more I look at the hstore type,
the more reasonable it seems. The table is just a serial, a timestamp, and
two columns 'old' and 'new'. The trigger function inserts these values
using hstore(OLD) and hstore(NEW).

Then, you can select old, new, and new - old, which returns an hstore of
what changed, or you could store this as a third column called 'delta' or
whatever. The hstore of course can be cast to a record, or any other
suitable object.

Of course, you could not store old and new, and only the delta if you
preferred, but it's nice to have both records.

Will anyone tell me there's some terrible side effect of this approach that
I am not realizing?


On Wed, Feb 6, 2013 at 11:10 AM, Greg Donald  wrote:

> On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver 
> wrote:
> > I have a wide-ish table with 60 columns. I want to make a copy of data
> > whenever a record is updated or deleted.
> >
> > Right now I have a table that's almost identical but with a 'created'
> column
> > (timestamp) and an 'action' column (which gets TG_OP for UPDATE or
> DELETE).
> >
> > My idea would be to sort on the created column to see the historical
> record
> > by comparing the columns. My other thought is to create two columns for
> each
> > column in the master table (old_column, new_column, etc), storing the old
> > values and the new values, and see what's changed that way.
> >
> > The other idea, probably a terrible idea, was to use hstore to create a
> list
> > of the old values and new values, and have this history table just be the
> > timestamp, action, and two hstore columns.
> >
> > Surely this has been done thousands of times. What are the thoughts
> > regarding best practices in PG?
>
> I handle this using middleware outside the db.  Past revisions of a
> record (from any table I want to track) are serialized into a JSON
> format and stored in a single table.  Postgres speaks JSON now, so..
>
>
> --
> Greg Donald
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Wells Oliver
wellsoli...@gmail.com

Re: [GENERAL] Keeping historical record changes w/ triggers - best practices?

[Greg Donald]

On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver  wrote:
> I have a wide-ish table with 60 columns. I want to make a copy of data
> whenever a record is updated or deleted.
>
> Right now I have a table that's almost identical but with a 'created' column
> (timestamp) and an 'action' column (which gets TG_OP for UPDATE or DELETE).
>
> My idea would be to sort on the created column to see the historical record
> by comparing the columns. My other thought is to create two columns for each
> column in the master table (old_column, new_column, etc), storing the old
> values and the new values, and see what's changed that way.
>
> The other idea, probably a terrible idea, was to use hstore to create a list
> of the old values and new values, and have this history table just be the
> timestamp, action, and two hstore columns.
>
> Surely this has been done thousands of times. What are the thoughts
> regarding best practices in PG?

I handle this using middleware outside the db.  Past revisions of a
record (from any table I want to track) are serialized into a JSON
format and stored in a single table.  Postgres speaks JSON now, so..


-- 
Greg Donald


-- 
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] configuring timezone

[Igor Neyman]

Terence,

Thanks for quick reply, I read your thread (Dec, 2012) before posting my 
question.
But, recompile is not an option for me.  Was hoping, that something regarding 
this issue changed since...

Igor Neyman

From: Terence Ferraro [mailto:terencejferr...@gmail.com]
Sent: Wednesday, February 06, 2013 1:45 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] configuring timezone

See the archived thread here: 
http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=q...@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want that 
behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2RF. 
I've deployed the patched version a couple dozen times now and it is working 
flawlessly.

T.J.
On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman 
mailto:iney...@perceptron.com>> wrote:
Timezone configuration parameter (defaulting to system timezone)  worked fine 
for us before upgrading from 8.4. to 9.2.

Now we've got a problem.
9.2 Release Notes says:

*  Identify the server time zone during initdb, and set postgresql.conf entries 
timezone
 and 
log_timezone
 accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server will 
probe system's timezone on startup (default to OS timezone on startup) instead 
setting it during initdb?
Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one timezone 
(could be anywhere around the globe), and then moved to other (not known during 
system build) location with different timezone.
After relocation, OS timezone will change, but we can't allow user to edit 
timezone parameter in  Postgresql.conf.

Regards,
Igor Neyman

Re: [GENERAL] configuring timezone

[Terence Ferraro]

See the archived thread here:
http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=q...@mail.gmail.com

Short version: Sorry, but you're going to need to recompile if you want
that behavior. Here's a diff applied against 9.2.1
http://pastebin.com/5AyaX2RF. I've deployed the patched version a couple
dozen times now and it is working flawlessly.

T.J.

On Wed, Feb 6, 2013 at 1:32 PM, Igor Neyman  wrote:

>  Timezone configuration parameter (defaulting to system timezone)  worked
> fine for us before upgrading from 8.4. to 9.2.
>
> ** **
>
> Now we’ve got a problem. 
>
> 9.2 Release Notes says:
>
> ·  Identify the server time zone during initdb, and set postgresql.confentries
> timezoneand
> log_timezoneaccordingly
>  (Tom Lane)
> 
>
> This avoids expensive time zone probes during server start.
>
> ** **
>
> Question: is there any way to revert back to old behavior so that server
> will probe system’s timezone on startup (default to OS timezone on startup)
> instead setting it during initdb?
>
> Obviously, without recompiling/rebuilding Postgres.
>
> ** **
>
> I’m dealing with the situation, where system is being built in one
> timezone (could be anywhere around the globe), and then moved to other (not
> known during system build) location with different timezone.  
>
> After relocation, OS timezone will change, but we can’t allow user to edit
> timezone parameter in  Postgresql.conf.
>
> ** **
>
> Regards,
>
> Igor Neyman
>

[GENERAL] Keeping historical record changes w/ triggers - best practices?

[Wells Oliver]

I have a wide-ish table with 60 columns. I want to make a copy of data
whenever a record is updated or deleted.

Right now I have a table that's almost identical but with a 'created'
column (timestamp) and an 'action' column (which gets TG_OP for UPDATE or
DELETE).

My idea would be to sort on the created column to see the historical record
by comparing the columns. My other thought is to create two columns for
each column in the master table (old_column, new_column, etc), storing the
old values and the new values, and see what's changed that way.

The other idea, probably a terrible idea, was to use hstore to create a
list of the old values and new values, and have this history table just be
the timestamp, action, and two hstore columns.

Surely this has been done thousands of times. What are the thoughts
regarding best practices in PG?

Thanks everyone.

-- 
Wells Oliver
wellsoli...@gmail.com

[GENERAL] configuring timezone

[Igor Neyman]

Timezone configuration parameter (defaulting to system timezone)  worked fine 
for us before upgrading from 8.4. to 9.2.

Now we've got a problem.
9.2 Release Notes says:

*  Identify the server time zone during initdb, and set postgresql.conf entries 
timezone
 and 
log_timezone
 accordingly (Tom Lane)

This avoids expensive time zone probes during server start.

Question: is there any way to revert back to old behavior so that server will 
probe system's timezone on startup (default to OS timezone on startup) instead 
setting it during initdb?
Obviously, without recompiling/rebuilding Postgres.

I'm dealing with the situation, where system is being built in one timezone 
(could be anywhere around the globe), and then moved to other (not known during 
system build) location with different timezone.
After relocation, OS timezone will change, but we can't allow user to edit 
timezone parameter in  Postgresql.conf.

Regards,
Igor Neyman

Re: [GENERAL] best config

[Steven Schlansker]

On Feb 6, 2013, at 9:55 AM, Roberto Scattini  wrote:

> 
> hi steven,
> 
> > we have two new dell poweredge r720. based on recommendations from this 
> > list we have configued the five disks in raid10 + 1 hot spare.
> 
> You might mention a bit more about how your drives are configured.  5 drives 
> in a RAID1+0 sounds odd to me.
> 
> 
> i mean, 4 disks in raid10, plus one disk as hot spare.
> 
> also, wasn't this list where recommended this setup, was in debian-user.
>  

That makes a lot more sense.  Nothing wrong with that setup :-)

> >
> > now we are looking for advice in the postgres installation for our setup.
> >
> > we have two databases. one for a lot of small apps and one for one big app 
> > with a lot of data and a lot of usage.
> > we want to use streaming replication to have a functional copy of databases 
> > in a failure.
> >
> > one of the ideas is to have one database running on each server, and then 
> > have another instance of the other database running in streaming 
> > replication (i mean, crossed replications).
> >
> > the other idea is to have both databases running in one server and backup 
> > everything in the other with streaming replication.
> >
> > which alternative would you use?
> 
> I would not introduce the complexity of having each server be master for half 
> of the data unless you can show that this improves some metric you care a lot 
> about.  Any failure or maintenance event will revert you back to the common 
> configuration -- back to having both masters on one system -- until you do 
> another promotion back to the "cross wired" setup.  Extra work without a 
> proposed gain.
> 
> Plus then you can get away with half as many Postgres installs to maintain.
> 
> ok. we thought in this crossed-replication config because one heavy query in 
> one of the databases wouldnt affect the performance of the other. 

Both of your servers need to be powerful enough to handle the whole load, 
otherwise your replication setup will not continue to function acceptably when 
one of the servers is offline due to a crash or maintenance.

I don't think there is anything necessarily wrong with your proposal, I am just 
pointing out that simplicity is better than complexity unless you can prove 
(say, in a test environment) that your application actually performs "better 
enough to justify the administrative cost" with this cross-wired setup.



-- 
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] best config

[Steven Schlansker]

On Feb 6, 2013, at 8:14 AM, Roberto Scattini  wrote:

> hi list,
> 
> we have two new dell poweredge r720. based on recommendations from this list 
> we have configued the five disks in raid10 + 1 hot spare.

You might mention a bit more about how your drives are configured.  5 drives in 
a RAID1+0 sounds odd to me.

> 
> now we are looking for advice in the postgres installation for our setup.
> 
> we have two databases. one for a lot of small apps and one for one big app 
> with a lot of data and a lot of usage.
> we want to use streaming replication to have a functional copy of databases 
> in a failure.
> 
> one of the ideas is to have one database running on each server, and then 
> have another instance of the other database running in streaming replication 
> (i mean, crossed replications).
> 
> the other idea is to have both databases running in one server and backup 
> everything in the other with streaming replication.
> 
> which alternative would you use?

I would not introduce the complexity of having each server be master for half 
of the data unless you can show that this improves some metric you care a lot 
about.  Any failure or maintenance event will revert you back to the common 
configuration -- back to having both masters on one system -- until you do 
another promotion back to the "cross wired" setup.  Extra work without a 
proposed gain.

Plus then you can get away with half as many Postgres installs to maintain.



-- 
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] ERROR: invalid input syntax for integer: ""

[Tom Lane]

Ben Madin  writes:
> On 2013-02-06, at 13:42 , Tom Lane  wrote:
>> The only part of this query that looks like it could possibly produce
>> that error is the res8.resultvalue-to-int cast:

>>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 
>>> 108 AND res8.del = false
>>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = 
>>> res8.resultvalue::int
>>  
>> ^
>> Presumably, there are some empty strings in results.resultvalue, and if
>> the query happens to try to compare one of them to rlu8.id, kaboom.

> Yes - this would be the case if it tried to match it against the resultvalue 
> only - some of the values in the table are NULL, but not for this 
> resulttypeid.

NULLs are not the problem (casting a NULL to anything is still a NULL).
The problem you've got is with empty strings, which are not at all the
same thing, even if Oracle can't tell the difference.

> So my understanding, working left to right was that the res.8 table rows 
> should be limited to those rows which have a resulttypeid = 108.

Please recall the section in the fine manual where it points out that
WHERE clauses are not evaluated left-to-right.  In the case at hand
I think the planner may be able to rearrange the join order, such that
the rlu8 join is done first.  Now, having said that, I'm not real sure
why the res8.resulttypeid = 108 clause couldn't be applied at scan level
not join level.  But you really need to be looking at EXPLAIN output
rather than theorizing about what order the clauses will be checked in.

> I'm really not sure what to do here.

You need to make sure the join clause is safe to evaluate for any data
present in the table.  The first question I'd ask is why isn't
resultvalue of a numeric type to start with --- this whole problem
smells of crummy schema design.  Or at least, why can't you use NULL
for the offending values instead of empty strings.  If you really can't
fix the data representation, you need to complicate the join clause to
make it not try to convert non-integral strings to ints.  One possible
solution is "nullif(res8.resultvalue, '')::int", if empty strings are
the only hazard.  If they're not, you could do something with a CASE
expression using a regex test on the string...

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] ERROR: invalid input syntax for integer: ""

[Adrian Klaver]

On 02/06/2013 01:28 AM, Ben Madin wrote:

Thanks Tom,

On 2013-02-06, at 13:42 , Tom Lane  wrote:


The only part of this query that looks like it could possibly produce
that error is the res8.resultvalue-to-int cast:



LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 
AND res8.del = false
LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = 
res8.resultvalue::int


^
Presumably, there are some empty strings in results.resultvalue, and if
the query happens to try to compare one of them to rlu8.id, kaboom.



Yes - this would be the case if it tried to match it against the resultvalue 
only - some of the values in the table are NULL, but not for this resulttypeid.




I'm really not sure what to do here.


When I run into issues like this I start over from scratch and build the 
query up a layer at a time using the minimum information necessary. Once 
I get the 'simple' case working then I start adding in more information.




cheers

Ben







--
Adrian Klaver
adrian.kla...@gmail.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] Passing dynamic parameters to a table-returning function

[Moshe Jacobson]

Perfect, that is exactly what I needed. Thanks David!


On Mon, Feb 4, 2013 at 6:49 PM, David Johnston  wrote:

> Moshe Jacobson wrote
> >  I'm
> > unsure of the syntax for passing in values from columns in the database
> as
> > the parameters of a set-returning function from which I want to select *.
>
> General form for this in version <= 9.2:
>
> WITH func_call_cte AS (
> SELECT func_call(col1, col2, col3) AS func_result
> FROM table_with_cols_123
> )
> SELECT (func_result).*
> FROM func_call_cte;
>
> 9.3 will have "LATERAL" and so should be much cleaner.
>
> The general idea is you have to put the function into the select-list so it
> can see the columns of the table in the FROM.  To avoid multiple
> evaluations
> of the function you have to treat the output set as a single typed column
> (func_result) and then in an outer query layer (in this case outside the
> WITH) you can generically expand the typed column into its component parts.
>
> HTH,
>
> David J.
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Passing-dynamic-parameters-to-a-table-returning-function-tp5743680p5743726.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
mo...@neadwerx.com | www.neadwerx.com

"People don't multitask because they're good at it. They do it because they
are more distracted" -- David
Sanbonmatsu

Re: [GENERAL] DEFERRABLE NOT NULL constraint

[Alban Hertroys]

On 6 February 2013 12:56, Chris Angelico  wrote:

> If you get into a taxi and ask
> to be driven to New Zealand within the hour, no amount of begging will
> get you what you want.
>

...Unless you get into a taxi in New Zealand.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: [GENERAL] Need help understanding WAL and checkpoints

[Albe Laurenz]

drew_hunt wrote:
> I'm trying to get my head around WAL and checkpoints and need to ask a couple 
> of questions before I
> get a headache.
> 
> Firstly, I see the terms "WAL log", "WAL file" and "transaction log" all over 
> the place - are these
> the same thing (i.e. files in the pg_xlog directory)?

Usually they mean the same thing.
For exact definitions, read
http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL
"WAL" consists of a stream of "WAL records" and is physically
represented as "WAL segment files" (in pg_xlog).

> I'm a bit confused by this paragraph in the docs:
> 
> "Checkpoints are points in the sequence of transactions at which it is 
> guaranteed that the heap and
> index data files have been updated with all information written before the 
> checkpoint. At checkpoint
> time, all dirty data pages are flushed to disk and a special checkpoint 
> record is written to the log
> file. (The changes were previously flushed to the WAL files.)"
> 
> ( see: http://www.postgresql.org/docs/current/static/wal-configuration.html )
> 
> "a special checkpoint record is written to the log file."
>     -> which log file is meant here?

The WAL.

> "The changes were previously flushed to the WAL files."
>    -> does "previously" here mean "at a previous point in time" or "in 
> previous PostgreSQL versions"?

The former.

>    -> at what point are changes flushed to WAL files?

The change must be on disk in a WAL segment before the
transaction can commit.

> So say I perform an operation like :
> 
>   UPDATE foo SET bar='baz'
> 
> are the following assumptions correct?
> 
> - The first time this changed data hits the disk, it is as an entry in the 
> WAL log
> 
> - At some point a checkpoint occurs, and the changed data is written to the 
> actual data file from
> system memory (the "dirty data pages"?)
> 
> - the only time the actual data files will be updated from the WAL log (i.e. 
> not from system memory)
> will be after a crash, when the logs are replayed from the last checkpoint?

All three are correct.

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] DEFERRABLE NOT NULL constraint

[Chris Angelico]

On Wed, Feb 6, 2013 at 10:36 PM, Bèrto ëd Sèra  wrote:
> Hi
>
>> I still don't see how that's any better than a stored procedure that
>> directly does the INSERT. You can conceal the code every bit as
>> easily.
>
> Guys I DO NOT write the customers' security guidelines. I get asked to
> produce a design in which "party X will make plain INSERTs and ignore
> the very existence of business rules". Can I do it in PG, No. Can I
> rewrite the guidelines? No. Hence, PG is not used. Full stop.

Sometimes you just have to tell the customer that his/her requirements
are impossible to plausibly implement. If you get into a taxi and ask
to be driven to New Zealand within the hour, no amount of begging will
get you what you want.

ChrisA


-- 
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] DEFERRABLE NOT NULL constraint

[Bèrto ëd Sèra]

Hi

> I still don't see how that's any better than a stored procedure that
> directly does the INSERT. You can conceal the code every bit as
> easily.

Guys I DO NOT write the customers' security guidelines. I get asked to
produce a design in which "party X will make plain INSERTs and ignore
the very existence of business rules". Can I do it in PG, No. Can I
rewrite the guidelines? No. Hence, PG is not used. Full stop.

Whether these customers are clever or stupid is not an issue. They are
paying customers, so they are right by design. And yes, sometimes I
manage to sell them something else, as I said earlier. Some other
times I end up having to use a db that is not PG. Easy as that.

Cheers
Bèrto

-- 
==
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.


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

[GENERAL] Need help understanding WAL and checkpoints

[drew_hunt1976]

Hi there

I'm trying to get my head around WAL and checkpoints and need to ask a couple 
of questions before I get a headache.

Firstly, I see the terms "WAL log", "WAL file" and "transaction log" all over 
the place - are these the same thing (i.e. files in the pg_xlog directory)?

I'm a bit confused by this paragraph in the docs:

"Checkpoints are points in the sequence of transactions at which it is 
guaranteed that the heap and index data files have been updated with all 
information written before the checkpoint. At checkpoint time, all dirty data 
pages are flushed to disk and a special checkpoint record is written to the log 
file. (The changes were previously flushed to the WAL files.)"

( see: http://www.postgresql.org/docs/current/static/wal-configuration.html )

"a special checkpoint record is written to the log file."
    -> which log file is meant here?

"The changes were previously flushed to the WAL files."
   -> does "previously" here mean "at a previous point in time" or "in previous 
PostgreSQL versions"?

   -> at what point are changes flushed to WAL files?

So say I perform an operation like :

  UPDATE foo SET bar='baz'

are the following assumptions correct?

- The first time this changed data hits the disk, it is as an entry in the WAL 
log

- At some point a checkpoint occurs, and the changed data is written to the 
actual data file from system memory (the "dirty data pages"?)

- the only time the actual data files will be updated from the WAL log (i.e. 
not from system memory) will be after a crash, when the logs are replayed from 
the last checkpoint?

thanks for any help on this!

- drew


-- 
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] "explain analyze" a procedure verbosely - to find which statement in it takes longer

[Alban Hertroys]

On 6 February 2013 11:03, Alexander Farber wrote:

> begin
>
> insert into pref_ban2 select
> id,
> first_name,
> last_name,
> city,
> last_ip
> from pref_users where id=_id;
>
> update pref_ban2 set reason=_reason where id=_id;
>

It's not the answer to your query, but why don't you do the above in a
single statement?

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: [GENERAL] "explain analyze" a procedure verbosely - to find which statement in it takes longer

[Pavel Stehule]

Hello


2013/2/6 Alexander Farber :
> Hello,
>
> I've read in the docs, that every table should
> better have primary key and so I've rearranged
> my 8.4.13 database: added primary keys to
> each table (some of the primary keys are
> pairs of columns) and dropped all other indices.
>
> And I've probably dropped few indices too many,
> because a stored procedure takes very long now:
>
> # explain analyze select pref_delete_user('DE17795', 'agr. comment');
>QUERY PLAN
>
> 
> 
>  Result  (cost=0.00..0.26 rows=1 width=0) (actual 
> time=1144672.341..1144672.342
> rows=1 loops=1)
>  Total runtime: 1144672.457 ms
> (2 rows)
>
> Is there maybe a way to make the
> "explain analyze" output more verbose?
>
> Or do I have to run each of procedure's
> statements by hand, preprending them
> with "explain analyze"?
>

no, it is not possible

http://blog.guillaume.lelarge.info/index.php/post/2012/03/31/Profiling-PL/pgsql-functions

or autoexplain with active auto_explain.log_nested_statements

Regards

Pavel Stehule

> My code is below, thanks for any advices
>
> Regards
> Alex
>
> create or replace function pref_delete_user(_id varchar,
> _reason varchar) returns void as $BODY$
> begin
>
> insert into pref_ban2 select
> id,
> first_name,
> last_name,
> city,
> last_ip
> from pref_users where id=_id;
>
> update pref_ban2 set reason=_reason where id=_id;
>
> create temporary table temp_gids (gid int not null) on
> commit drop;
> insert into temp_gids (gid) select gid from
> pref_scores where id=_id;
>
> delete from pref_games p
> using temp_gids t
> where p.gid = t.gid;
>
> create temporary table temp_rids (rid int not null) on
> commit drop;
> insert into temp_rids (rid) select rid from pref_cards
> where id=_id;
>
> delete from pref_rounds r
> using temp_rids t
> where r.rid = t.rid;
>
> delete from pref_users where id=_id;
>
> end;
> $BODY$ language plpgsql;
>
> create table pref_users (
> id varchar(32) primary key,
> first_name varchar(64),
> last_name varchar(64),
> female boolean,
> avatar varchar(128),
> city varchar(64),
> login timestamp default current_timestamp,
> logout timestamp,
> last_ip inet,
> vip timestamp,
> mail varchar(256),
> medals integer not null default 0
> );
>
> create table pref_rounds (
> rid serial primary key,
> cards text,
> stamp timestamp default current_timestamp
> );
>
> create table pref_cards (
> rid integer references pref_rounds on delete cascade,
> id varchar(32) references pref_users on delete cascade,
> bid varchar(32) not null,
> trix integer not null,
> pos integer not null,
> money integer not null,
> last_ip inet,
> quit boolean,
> stamp timestamp default current_timestamp,
> primary key(id, rid)   /* added recently */
> );
>
> create table pref_games (
> gid serial primary key,
> rounds integer not null,
> stamp timestamp default current_timestamp
> );
>
> create table pref_scores (
> id varchar(32) references pref_users on delete cascade,
> gid integer references pref_games on delete cascade,
> money integer not null,
> last_ip inet,
> quit boolean,
> primary key(id, gid);  /* added recently */
> );
>
> create table pref_ban2 (
> id varchar(32) primary key,  /* not a foreign key,
> since banned */
> first_name varchar(64),
> last_name varchar(64),
> city varchar(64),
> last_ip inet,
> reason varchar(128),
> created timestamp default current_timestamp
> );
>
>
> --
> 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] "explain analyze" a procedure verbosely - to find which statement in it takes longer

[Alexander Farber]

Hello,

I've read in the docs, that every table should
better have primary key and so I've rearranged
my 8.4.13 database: added primary keys to
each table (some of the primary keys are
pairs of columns) and dropped all other indices.

And I've probably dropped few indices too many,
because a stored procedure takes very long now:

# explain analyze select pref_delete_user('DE17795', 'agr. comment');
   QUERY PLAN



 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=1144672.341..1144672.342
rows=1 loops=1)
 Total runtime: 1144672.457 ms
(2 rows)

Is there maybe a way to make the
"explain analyze" output more verbose?

Or do I have to run each of procedure's
statements by hand, preprending them
with "explain analyze"?

My code is below, thanks for any advices

Regards
Alex

create or replace function pref_delete_user(_id varchar,
_reason varchar) returns void as $BODY$
begin

insert into pref_ban2 select
id,
first_name,
last_name,
city,
last_ip
from pref_users where id=_id;

update pref_ban2 set reason=_reason where id=_id;

create temporary table temp_gids (gid int not null) on
commit drop;
insert into temp_gids (gid) select gid from
pref_scores where id=_id;

delete from pref_games p
using temp_gids t
where p.gid = t.gid;

create temporary table temp_rids (rid int not null) on
commit drop;
insert into temp_rids (rid) select rid from pref_cards
where id=_id;

delete from pref_rounds r
using temp_rids t
where r.rid = t.rid;

delete from pref_users where id=_id;

end;
$BODY$ language plpgsql;

create table pref_users (
id varchar(32) primary key,
first_name varchar(64),
last_name varchar(64),
female boolean,
avatar varchar(128),
city varchar(64),
login timestamp default current_timestamp,
logout timestamp,
last_ip inet,
vip timestamp,
mail varchar(256),
medals integer not null default 0
);

create table pref_rounds (
rid serial primary key,
cards text,
stamp timestamp default current_timestamp
);

create table pref_cards (
rid integer references pref_rounds on delete cascade,
id varchar(32) references pref_users on delete cascade,
bid varchar(32) not null,
trix integer not null,
pos integer not null,
money integer not null,
last_ip inet,
quit boolean,
stamp timestamp default current_timestamp,
primary key(id, rid)   /* added recently */
);

create table pref_games (
gid serial primary key,
rounds integer not null,
stamp timestamp default current_timestamp
);

create table pref_scores (
id varchar(32) references pref_users on delete cascade,
gid integer references pref_games on delete cascade,
money integer not null,
last_ip inet,
quit boolean,
primary key(id, gid);  /* added recently */
);

create table pref_ban2 (
id varchar(32) primary key,  /* not a foreign key,
since banned */
first_name varchar(64),
last_name varchar(64),
city varchar(64),
last_ip inet,
reason varchar(128),
created timestamp default current_timestamp
);


-- 
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] ERROR: invalid input syntax for integer: ""

[Ben Madin]

Thanks Tom,

On 2013-02-06, at 13:42 , Tom Lane  wrote:

> The only part of this query that looks like it could possibly produce
> that error is the res8.resultvalue-to-int cast:

>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 
>> AND res8.del = false
>> LEFT JOIN resultlookup rlu8 ON rlu8.resulttypesid = 108 AND rlu8.id = 
>> res8.resultvalue::int
>
> ^
> Presumably, there are some empty strings in results.resultvalue, and if
> the query happens to try to compare one of them to rlu8.id, kaboom.


Yes - this would be the case if it tried to match it against the resultvalue 
only - some of the values in the table are NULL, but not for this resulttypeid.

So my understanding, working left to right was that the res.8 table rows should 
be limited to those rows which have a resulttypeid = 108. These all have 
numeric values, vis :

select distinct resultvalue from results where resulttypeid  = 108 order by 
resultvalue; 
 resultvalue 
-
   932.0
   933.0
   934.0
   935.0
   936.0
   937.0
   938.0
   939.0
   940.0
  3224.0
(10 rows)

and it should then be only these rows that are joined to the resultlookup 
table… but it seems that the rlu8.id = res8.resultvalue is being done first.

Can I prevent that? Using a subquery, or a some other approach.

> The way that the error comes and goes depending on seemingly-irrelevant
> changes isn't too surprising.  Probably what's happening is that the
> query plan changes around so that that test occurs earlier or later
> relative to other join clauses.

That might just be it - the query explain is different for the same query on 
each machine. 

Just to confuse the issue, if I take the resultlookup table out completely, I 
still get the same error. So maybe it isn't that join at all that is raising 
the error.

If I take the results table out… it works(the commented code below being the 
change.) 

SELECT rep.id, --res.reportid, 
round(st_distance_sphere('010120BB108716D9CEF7A36240643BDF4F8DA741C0', 
post.the_point)/1000) as dist 
FROM reports rep 
LEFT JOIN users u ON rep.link = u.id 
LEFT JOIN postcodes post ON u.postcode::integer = post.postcode  
LEFT JOIN species spe ON rep.species::text like spe.speccode::text AND 
spe.synonym = 0  
--LEFT JOIN results res ON rep.id = res.reportid  AND res.resulttypeid = 108 
AND res.del is false
WHERE rep.del IS false AND rep.projectid = 51
AND round(st_distance_sphere( 
'010120BB108716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000) < 
150
AND spe.id = 9465;

I'm really not sure what to do here.

cheers

Ben




-- 

Ben Madin

m : +61 448 887 220
e : b...@ausvet.com.au


-- 
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] DEFERRABLE NOT NULL constraint

[Chris Angelico]

On Wed, Feb 6, 2013 at 8:01 PM, Jasen Betts  wrote:
> On 2013-02-06, Bèrto ëd Sèra  wrote:
>> Hi
>>
>>> You've hidden nothing from INSERT-RETURNING.
>>
>> ?? Or from a select, if the final value is what you mean. What we hide
>> is the way values are made, clearly not the final value. That bit is
>> accessible to anyone who can select the table, obviously.
>>
>
> so the trigger function is opaque, written in C or some other language
> where they can't access the source easily?

I still don't see how that's any better than a stored procedure that
directly does the INSERT. You can conceal the code every bit as
easily.

ChrisA


-- 
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] DEFERRABLE NOT NULL constraint

[Jasen Betts]

On 2013-02-06, Bèrto ëd Sèra  wrote:
> Hi
>
>> You've hidden nothing from INSERT-RETURNING.
>
> ?? Or from a select, if the final value is what you mean. What we hide
> is the way values are made, clearly not the final value. That bit is
> accessible to anyone who can select the table, obviously.
>

so the trigger function is opaque, written in C or some other language
where they can't access the source easily?




-- 
⚂⚃ 100% natural



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