Re: [GENERAL] python modul pre-import to avoid importing each time

[Rémi Cura]

Hey,
thanks for your answer !

Yep you are right, the function I would like to test are going to be called
a lot (100k times), so even 15 ms per call matters.

I'm still a bit confused by a topic I found here :
http://stackoverflow.com/questions/15023080/how-are-import-statements-in-plpython-handled
The answer gives a trick to avoid importing each time, so somehow it must
be usefull.

On another internet page (can't find it anymore) somebody mentioned this
module loading at server startup, one way or another, but gave no
precision. It seems that the "plpy" python module get loaded by default,
would'nt it be possible to hack this module to add other import inside it?

I also use PL/R (untrusted I guess) and you can create a special table to
indicate which module to load at startup.

Cheers,
Rémi-C



2014-06-25 21:46 GMT+02:00 Jeff Janes :

> On Thu, Jun 19, 2014 at 7:50 AM, Rémi Cura  wrote:
> > Hey List,
> >
> > I use plpython with postgis and 2 python modules (numpy and shapely).
> > Sadly importing such module in the plpython function is very slow
> (several
> > hundreds of milliseconds).
>
> Is that mostly shapely (which I don't have)?  numpy seems to be pretty
> fast, like 16ms.  But that is still slow for what you want, perhaps.
>
> >
> > I also don't know if this overhead is applied each time the function is
> > called in the same session.
>
> It is not.  The overhead is once per connection, not once per call.
> So using a connection pooler could be really be a help here.
>
> > Is there a way to pre-import those modules once and for all,
> > such that the python function are accelerated?
>
> I don't think there is.  With plperl you can do this by loading the
> module in plperl.on_init and by putting plperl into
> shared_preload_libraries so that this happens just at server start up.
> But I don't see a way to do something analogous for plpython due to
> lack of plpython.on_init.  I think that is because the infrastructure
> to do that is part of making a "trusted" version of the language,
> which python doesn't have.  (But it could just be that no one has ever
> gotten around to adding it.)
>
> Cheers,
>
> Jeff
>

Re: [GENERAL] Alternative to psql -c ?

[James Le Cuirot]

On Wed, 25 Jun 2014 09:04:44 -0700
Tom Lane  wrote:

> James Le Cuirot  writes:
> > hubert depesz lubaczewski  wrote:
> >> Perhaps you can explain what is the functionality you want to
> >> achieve, as I, for one, don't understand. Do you want transactions?
> >> Or not?
> 
> > I want an implicit transaction around the whole script if no
> > explicit transactions are present in the script. This is what
> > psql's -c option does and this is also what the pg gem does because
> > both use PQexec.

> In short, I think it would be good to push back on the way Chef is
> doing things now, not perpetuate a dependency on a legacy behavior.

The mention of "legacy behaviour" and "unexpected results" in the
psql man page hadn't gone unnoticed but I didn't think I would be able
to convince the Chef guys to change their approach based on that. I
think I stand a much better chance now that you of all people have said
it though so thanks. :)

This got me wondering what Rails uses. I dug into ActiveRecord and
found that apart from the odd call to PQexec with hardcoded single
statements, it uses PQsendQuery. The libpq docs state a few of the
differences but don't mention whether PQsendQuery automatically creates
a transaction like PQexec does. Please could you clarify this?

Regards,
James


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

[GENERAL] Fast data, slow data

[Tim Uckun]

I have a use case in which the most recent data experiences a lot of
transactions (inserts and updates) and then the churn kind of calms down.
 Eventually the data is relatively static and will only be updated in
special and sporatic events.

I was thinking about keeping the high churn data in a different table so
that the vacuums on that table can go faster and the vacuums on the rest of
the data will rarely be needed.

Ideally the high churn data would be in an in memory table but failing that
I thought that an unlogged table would be good enough.  So now I need a way
to flush the oldest data in this table to another logged table and do it in
an efficient manner. I don't think partitioning is a good idea in this case
because the partitions will be for small time periods (5 to 15 minutes).

Anybody have a similar problem? If so how did you solve it?

Thanks.

Re: [GENERAL] Alternative to psql -c ?

[James Le Cuirot]

On Wed, 25 Jun 2014 13:21:44 -0500
Merlin Moncure  wrote:

> > The cookbook currently uses PQexec so multiple SQL commands are
> > wrapped in a transaction unless an explicit transaction
> > instruction appears. I don't want to change this behaviour but
> > the only way to get exactly the same effect from psql is to use
> > the -c option.
> >
> > I suspect some may shove rather large SQL scripts through this to
> > the extent that it may break the command line limit, if not on
> > Linux, then perhaps on Windows, where I gather it's 32,768. Passing
> > these scripts on the command line doesn't seem particularly elegant
> > in any case. I'd really like to use stdin but this has different
> > transactional behaviour. I thought about looking for instances of
> > transaction instructions in advance but I have seen that PostgreSQL
> > does not do this naively; it uses the lexer.
> >
> > Is there another way?  
> 
> If I understand you properly (not sure), I pretty commonly get around
> this via 'cat'.
> 
> cat \
>   <(echo "BEGIN;") \
>   <(echo "\set ON_ERROR_STOP") \
>   foo.sql bar.sql etc
>   <(echo "COMMIT;") \
>   | psql ...

This would work but given that this will be part of a public and
widely-used cookbook, it needs to be able to deal with any scripts that
will be thrown at it. Some of these may contain transactional
statements and these will not work properly if wrapped in a big
BEGIN/COMMIT. Having said that, Tom Lane has suggested that we should
not rely on the existing transactional behaviour so maybe we'll need to
be more explicit about whether we actually want a transaction or not.

Thanks anyway,
James


-- 
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] Alternative to psql -c ?

[James Le Cuirot]

On Wed, 25 Jun 2014 10:34:57 -0500
Jerry Sievers  wrote:

> > The cookbook currently uses PQexec so multiple SQL commands are
> > wrapped in a transaction unless an explicit transaction
> > instruction appears. I don't want to change this behaviour but
> > the only way to get exactly the same effect from psql is to use
> > the -c option.
> >
> > I suspect some may shove rather large SQL scripts through this to
> > the extent that it may break the command line limit, if not on
> > Linux, then perhaps on Windows, where I gather it's 32,768. Passing
> > these scripts on the command line doesn't seem particularly elegant
> > in any case. I'd really like to use stdin but this has different
> > transactional behaviour. I thought about looking for instances of
> > transaction instructions in advance but I have seen that PostgreSQL
> > does not do this naively; it uses the lexer.
> >
> > Is there another way?  
> 
> Forget about trying to use psql -c since you try doing anything
> non-trivial via this method and  quoting will be at least one of your
> headaches.

Ruby executes psql using a kernel exec call and each argument is passed
distinctly without any quoting required so that doesn't seem to be a
problem at least.

> Write a simpel $your-fav-scripting-lang client that passes stdin into
> a single executor call...
> 
> #!/usr/bin/python
> 
> import psycopg2, sys
> 
> conn = psycopg2.connect(...)
> cur = conn.cursor(
> 
> cur.execute(sys.stdin.read())
> conn.commit()

This would work but probably wouldn't fly with the Chef guys as they'd
want to know why it's installing modules for $my-fav-scripting-lang,
which may != $their-fav-scripting-lang. Chances are that this would be
Ruby in both cases but not every system packages the pg gem and that
leads to the build-essential headache.

> PS: Complex multi-statement executor calls are somewhat nuanced in
> their own ways and I would be trying hard *not* to do this without
> very good reason.

Tom Lane has since said as much so I agree that a different approach is
needed. I'll see if I can convince them.

James


-- 
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] Alternative to psql -c ?

[James Le Cuirot]

On Wed, 25 Jun 2014 17:30:15 +0200
hubert depesz lubaczewski  wrote:

> On Wed, Jun 25, 2014 at 5:18 PM, James Le Cuirot
>  wrote:
> 
> > > Also - I have no idea what "peer authentication" has to do with Pg
> > > gem - care to elaborate? The gem is for client, and authentication
> > > happens in server, so ... ?
> > Right but peer authentication is all to do with the operating system
> > user that the client is connecting from. In the case of chef-client,
> >
> 
> Any reason why you can't reconfigure Pg to allow root connections to
> postgres account?

I had considered this. In practise, the "end user" would also need to
be added. The postgresql cookbook currently doesn't generate the
pg_ident.conf file but it could.

However, this would still require the installation of build-essential
and Tom Lane has pointed out that using PQexec is flawed anyway so
maybe I could use stdin after all.

James


-- 
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] Alternative to psql -c ?

[Merlin Moncure]

On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot
 wrote:
> On Wed, 25 Jun 2014 13:21:44 -0500
> Merlin Moncure  wrote:
>
>> > The cookbook currently uses PQexec so multiple SQL commands are
>> > wrapped in a transaction unless an explicit transaction
>> > instruction appears. I don't want to change this behaviour but
>> > the only way to get exactly the same effect from psql is to use
>> > the -c option.
>> >
>> > I suspect some may shove rather large SQL scripts through this to
>> > the extent that it may break the command line limit, if not on
>> > Linux, then perhaps on Windows, where I gather it's 32,768. Passing
>> > these scripts on the command line doesn't seem particularly elegant
>> > in any case. I'd really like to use stdin but this has different
>> > transactional behaviour. I thought about looking for instances of
>> > transaction instructions in advance but I have seen that PostgreSQL
>> > does not do this naively; it uses the lexer.
>> >
>> > Is there another way?
>>
>> If I understand you properly (not sure), I pretty commonly get around
>> this via 'cat'.
>>
>> cat \
>>   <(echo "BEGIN;") \
>>   <(echo "\set ON_ERROR_STOP") \
>>   foo.sql bar.sql etc
>>   <(echo "COMMIT;") \
>>   | psql ...
>
> This would work but given that this will be part of a public and
> widely-used cookbook, it needs to be able to deal with any scripts that
> will be thrown at it. Some of these may contain transactional
> statements and these will not work properly if wrapped in a big
> BEGIN/COMMIT. Having said that, Tom Lane has suggested that we should
> not rely on the existing transactional behaviour so maybe we'll need to
> be more explicit about whether we actually want a transaction or not.

To be clear, Tom was advising not to rely on some of the quirky
aspects of -c.  psql as it stands right now has a some limitations:
single transaction mode does not work with stdin and there is no
reasonable way to pass multiple scripts through the command line.
Adding it up this means that for generic multiple .sql passing you
have to wrap psql with a script.

It'd be neat if psql had some xargs compatible facility for passing
multiple files.  This is complicated by the assumption that the
unadorned argument is the database.  Suppose though if the -f switch
is arguments past the first are assumed to be files. Then you could
do:

psql foodb -1f foo.sql bar.sql baz.sql

or find . | xargs psql foodb -1f

(it's arguably cleaner to allow multiple -f arguments, but that's a
lot more problematic for xargs type usage).

As things stand today though, AFAICT the best way to consolidate
scripts is to build a big script out of small ones.  I realize that
stinks from your point of view since not everyone will want to use
unix/bash...

merlin


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

Re: [GENERAL] Alternative to psql -c ?

[James Le Cuirot]

On Thu, 26 Jun 2014 07:23:02 -0500
Merlin Moncure  wrote:

> On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot
>  wrote:
> > On Wed, 25 Jun 2014 13:21:44 -0500
> > Merlin Moncure  wrote:
> >
> >> > The cookbook currently uses PQexec so multiple SQL commands are
> >> > wrapped in a transaction unless an explicit transaction
> >> > instruction appears. I don't want to change this behaviour but
> >> > the only way to get exactly the same effect from psql is to use
> >> > the -c option.
> >> >
> >> > I suspect some may shove rather large SQL scripts through this to
> >> > the extent that it may break the command line limit, if not on
> >> > Linux, then perhaps on Windows, where I gather it's 32,768.
> >> > Passing these scripts on the command line doesn't seem
> >> > particularly elegant in any case. I'd really like to use stdin
> >> > but this has different transactional behaviour. I thought about
> >> > looking for instances of transaction instructions in advance but
> >> > I have seen that PostgreSQL does not do this naively; it uses
> >> > the lexer.
> >> >
> >> > Is there another way?
> >>
> >> If I understand you properly (not sure), I pretty commonly get
> >> around this via 'cat'.
> >>
> >> cat \
> >>   <(echo "BEGIN;") \
> >>   <(echo "\set ON_ERROR_STOP") \
> >>   foo.sql bar.sql etc
> >>   <(echo "COMMIT;") \
> >>   | psql ...
> >
> > This would work but given that this will be part of a public and
> > widely-used cookbook, it needs to be able to deal with any scripts
> > that will be thrown at it. Some of these may contain transactional
> > statements and these will not work properly if wrapped in a big
> > BEGIN/COMMIT. Having said that, Tom Lane has suggested that we
> > should not rely on the existing transactional behaviour so maybe
> > we'll need to be more explicit about whether we actually want a
> > transaction or not.
> 
> To be clear, Tom was advising not to rely on some of the quirky
> aspects of -c.  psql as it stands right now has a some limitations:
> single transaction mode does not work with stdin and there is no
> reasonable way to pass multiple scripts through the command line.
> Adding it up this means that for generic multiple .sql passing you
> have to wrap psql with a script.

I never said that I wanted to pass multiple scripts, just singular
scripts who's contents might be too large for the command line limit,
and that they might contain transaction statements.

I was looking for some way to replicate the -c behaviour without
actually passing the contents on the command line but Tom says I would
not want that behaviour anyway. So in that case, assuming I allow the
existing behaviour to be modified and make single transaction mode an
explicit option for the Chef users...

These scripts come from a Ruby string buffer so passing them via stdin
would be preferable to having to dump them out to a file first. You say
that single transaction mode doesn't work with stdin but it looks like
this was fixed in 9.3. I'd like this to work with earlier versions
though so maybe "psql -1 -f -" would be the way to go. The man page
says that this is subtly different from using < but doesn't mention
transactions specifically. I hope this works.

Regards,
James


-- 
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] python modul pre-import to avoid importing each time

[Adrian Klaver]

On 06/26/2014 02:14 AM, Rémi Cura wrote:

Hey,
thanks for your answer !

Yep you are right, the function I would like to test are going to be
called a lot (100k times), so even 15 ms per call matters.

I'm still a bit confused by a topic I found here :
http://stackoverflow.com/questions/15023080/how-are-import-statements-in-plpython-handled

The answer gives a trick to avoid importing each time, so somehow it
must be usefull.


Peters answer is based on using the global dictionary SD to store an 
imported library. For more information see here:


http://www.postgresql.org/docs/9.3/interactive/plpython-sharing.html



On another internet page (can't find it anymore) somebody mentioned this
module loading at server startup, one way or another, but gave no
precision. It seems that the "plpy" python module get loaded by default,
would'nt it be possible to hack this module to add other import inside it?


In a sense that is what is being suggested above.



I also use PL/R (untrusted I guess) and you can create a special table
to indicate which module to load at startup.

Cheers,
Rémi-C




--
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] Alternative to psql -c ?

[Merlin Moncure]

On Thu, Jun 26, 2014 at 7:59 AM, James Le Cuirot
 wrote:
> On Thu, 26 Jun 2014 07:23:02 -0500
> Merlin Moncure  wrote:
>> To be clear, Tom was advising not to rely on some of the quirky
>> aspects of -c.  psql as it stands right now has a some limitations:
>> single transaction mode does not work with stdin and there is no
>> reasonable way to pass multiple scripts through the command line.
>> Adding it up this means that for generic multiple .sql passing you
>> have to wrap psql with a script.
>
> I never said that I wanted to pass multiple scripts, just singular
> scripts who's contents might be too large for the command line limit,
> and that they might contain transaction statements.

yup.  Passing huge scripts via -c is definitely not a good practice.
-c is designed for things like firing off a stored procedure from cron
or bash variable assignment (for example: FOO=`psql -tXAqc"select
bar()"`)

> These scripts come from a Ruby string buffer so passing them via stdin
> would be preferable to having to dump them out to a file first. You say
> that single transaction mode doesn't work with stdin but it looks like
> this was fixed in 9.3.

yup -- i keep forgetting that.  too many years of not having it i suppose.

merlin


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

Re: [GENERAL] Fast data, slow data

[Shaun Thomas]

On 06/26/2014 04:29 AM, Tim Uckun wrote:


I don't think partitioning is a good idea in this case because the
partitions will be for small time periods (5 to 15 minutes).


Actually, partitioning might be exactly what you want, but not in the 
way you might think. What you've run into is actually a pretty common 
usage pattern. How we solve problems like this where I work is to use 
table inheritance alone. Consider this:


CREATE TABLE my_table
(
  ... columns
);

CREATE TABLE my_table_stable (INHERITS my_table);

Then you create a job that runs however often you want, and all that job 
does, is move old rows from my_table, to my_table_stable. Like so:


BEGIN;

INSERT INTO my_table_stable
SELECT * FROM ONLY my_table
 WHERE date_col >= now() - INTERVAL '15 minutes';

DELETE FROM ONLY my_table
 WHERE date_col >= now() - INTERVAL '15 minutes';

COMMIT;

Or whatever. But you get the idea.

This way, you still get all the data by selecting from my_table, but the 
data is partitioned in such a way that you can put the high turnover 
table in another tablespace, or otherwise modify it for performance reasons.


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] DATA corruption after promoting slave to master

[Shaun Thomas]

On 06/26/2014 09:44 AM, Karthik Iyer wrote:


We reindexed  all the primary and unique keys of all the tables, But we
did not reindex the tables. You think we should do that also ?


Yes, you need to reindex. Part of the problem with this kind of table 
corruption, is that PostgreSQL has applied data and index page 
modifications to the binary files themselves. Due to this, there are 
probably index rows pointing to incorrect or invalid data pages.


Reindexing the primary and unique indexes is a very good start, but any 
queries that use regular indexes can still return invalid or mismatched 
rows when compared to the primary/unique results. Those kind of 
mismatches can result in unexpected behavior from applications.



We managed to get the old server up. We are trying to play the
difference in data by checking the log files(statement logs). You think
there is any other easy alternatives ?


Probably not. Now that your slave has been promoted and had its own 
write activity, the old primary is not useful as a source of binary 
replay. You *can* use it to verify which rows are old/new while vetting 
out corruption, though. It's a good source of data for checking the 
consistency of the new primary.


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] Fast data, slow data

[Steve Crawford]

On 06/26/2014 02:29 AM, Tim Uckun wrote:
I have a use case in which the most recent data experiences a lot of 
transactions (inserts and updates) and then the churn kind of calms 
down.  Eventually the data is relatively static and will only be 
updated in special and sporatic events.


I was thinking about keeping the high churn data in a different table 
so that the vacuums on that table can go faster and the vacuums on the 
rest of the data will rarely be needed.


Ideally the high churn data would be in an in memory table but failing 
that I thought that an unlogged table would be good enough.  So now I 
need a way to flush the oldest data in this table to another logged 
table and do it in an efficient manner. I don't think partitioning is 
a good idea in this case because the partitions will be for small time 
periods (5 to 15 minutes).


Anybody have a similar problem? If so how did you solve it?


It's not at all unusual for a database to experience a high portion of 
its activity on a small subset of the data so in that sense the 
"problem" is already solved by appropriate sizing and tuning to make 
appropriate adjustments to the caching and other properties already 
provided by the OS and DB.


Having said that, there is far too little information here to provide 
specific advice on tuning and other approaches that may be of use for 
you. In particular:


Is this currently a hypothetical question or do you have a running 
system that is experiencing performance problems?


In either case can you provide some parameters including rows and size 
of your large table(s), what you mean by "high churn" (rate of inserts, 
deletes, updates and how they are grouped into transactions), the size 
of the table that is heavily updated, how the database is used 
(transactional, data-mining, ...) and anything else you feel might help 
the list understand your use-case.


If you have a live database, any metrics/observations you can supply 
might be helpful. For example, are particular queries slow? What speed 
is required? Do you have lots of simultaneous connections or is 
everything through a single connection.


Finally,  confirm that the data is not important or is easily recreated 
(implied by your in-memory/unlogged-table comments).


Cheers,
Steve




--
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] Fast data, slow data

[Marti Raudsepp]

On Thu, Jun 26, 2014 at 5:49 PM, Shaun Thomas  wrote:
> Then you create a job that runs however often you want, and all that job
> does, is move old rows from my_table, to my_table_stable. Like so:
>
> BEGIN;
> INSERT INTO my_table_stable
> SELECT * FROM ONLY my_table
>  WHERE date_col >= now() - INTERVAL '15 minutes';
> DELETE FROM ONLY my_table
>  WHERE date_col >= now() - INTERVAL '15 minutes';
> COMMIT;

This deserves a caveat, in the default "read committed" isolation
level, this example can delete more rows that it inserts; even if your
date_col never changes and you don't have transactions running for
that long, it's best not to use this pattern. You could change the
isolation using SET TRANSACTION, or much better, use wCTE to solve
this atomically:

WITH deleted AS (
  DELETE FROM ONLY my_table
  WHERE date_col >= now() - INTERVAL '15 minutes'
  RETURNING *
)
INSERT INTO my_table_stable
  SELECT * FROM deleted;

Regards,
Marti


-- 
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] Fast data, slow data

[Shaun Thomas]

On 06/26/2014 10:47 AM, Marti Raudsepp wrote:


This deserves a caveat, in the default "read committed" isolation
level, this example can delete more rows that it inserts;


This is only true because I accidentally inverted the date resolutions. 
It should have been:


BEGIN;
INSERT INTO my_table_stable
SELECT * FROM ONLY my_table
  WHERE date_col <= now() - INTERVAL '15 minutes';
DELETE FROM ONLY my_table
  WHERE date_col <= now() - INTERVAL '15 minutes';
COMMIT;

Since now() is tied to transaction start time, it's safe in this 
context. But you're right, I actually debated including the CTE syntax, 
but you did it for me. :)


Though you fell into the same trap I did. The correct CTE should be:

WITH deleted AS (
   DELETE FROM ONLY my_table
   WHERE date_col <= now() - INTERVAL '15 minutes'
   RETURNING *
)
INSERT INTO my_table_stable
SELECT * FROM deleted;

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] DATA corruption after promoting slave to master

[Shaun Thomas]

On 06/26/2014 10:34 AM, Karthik Iyer wrote:


Any inputs here? You think a pgdump and restore would help more ?


A full dump and restore would definitely help. I tend not to suggest 
that often because I work with very large databases that are usually 
extremely cumbersome to dump and restore.


But yeah, if you can get a successful pg_dump from your database, a 
restore should obviously clean up all of your data and index 
inconsistencies if you're willing to wait.


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


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

[GENERAL] Function Syntax Help

[Dennis Ryan]

I having trouble with correct syntax to get this trigger function to compile.  
I have tried every combination of removing the ‘;’ characters but the function 
will not compile.  Can someone tell me what I am doing wrong, I am stumped. I 
will be adding addition when clauses the case statement once I get this 
simplified version to compile.

I am using 9.3.4, both postgres and psql.


CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
CASE
WHEN NEW.period =  201001
THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
END;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;


ERROR:  syntax error at or near ";"
LINE 7: END;

Re: [GENERAL] DATA corruption after promoting slave to master

[Karthik Iyer]

Thanks Shaun.

We reindexed  all the primary and unique keys of all the tables, But we 
did not reindex the tables. You think we should do that also ?


Also, do you think we should do a clean dump restore to eliminate all 
data inconsistencies.


One more query :

We managed to get the old server up. We are trying to play the 
difference in data by checking the log files(statement logs). You think 
there is any other easy alternatives ?


Thanks in advance,

- Karthik


On Thursday 26 June 2014 01:09 AM, Shaun Thomas wrote:

On 06/25/2014 06:29 AM, Karthik Iyer wrote:


[2]  We also have a daemon process which copies the latest partial WAL
log file (which postgres is currently writing to, under pg_xlog/) every
3 secs to a different location.


No. No, no, no, no no. No.

Also, no. Partial WAL files are not valid for recovery. In fact, I'm 
surprised the standby node even applied it at all.



We are seeing these problems in the newly promoted master now:

 1. when we run queries on primary key, we don't get the rows even
if it exist in db. However if we force query not to use index, we get
those entries.
 2. there are duplicate values for primary keys


This is no surprise. Your slave has partial data commits, which means 
your table, index, or possibly both, are corrupt.


The first thing you need to do is back up any tables you've noticed 
are having this behavior. Second, try to reindex the tables that are 
having problems. The errors you are seeing are due to the data and 
indexes being out of sync. If you get an error that says the reindex 
fails due to duplicate values, you can do this:


SET enable_indexscan TO false;
SET enable_bitmapscan TO false;

SELECT primary_key, count(1)
  FROM broken_table
 GROUP BY 1
HAVING count(1) > 1;

For any ID that comes back, do this:

SELECT ctid, *
  FROM broken_table
 WHERE primary_key = [value(s) from above];

Then you need to delete one of the bad rows after deciding which. Use 
the CTID of the row you want to delete:


DELETE FROM broken_table
 WHERE ctid = 'whatever';

Then reindex the table so the correct values are properly covered. 
Doing this for all of your corrupt tables may take a while depending 
on how many there are.






--
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] DATA corruption after promoting slave to master

[Karthik Iyer]

Hi Shaun,




We reindexed  all the primary and unique keys of all the tables, But we
did not reindex the tables. You think we should do that also ?


Yes, you need to reindex. Part of the problem with this kind of table 
corruption, is that PostgreSQL has applied data and index page 
modifications to the binary files themselves. Due to this, there are 
probably index rows pointing to incorrect or invalid data pages.


Reindexing the primary and unique indexes is a very good start, but 
any queries that use regular indexes can still return invalid or 
mismatched rows when compared to the primary/unique results. Those 
kind of mismatches can result in unexpected behavior from applications.


Thanks, we will reindex the tables too.

Also, do you think we should do a clean dump restore to eliminate all 
data inconsistencies.


Any inputs here? You think a pgdump and restore would help more ?

TIA,

- Karthik

Re: [GENERAL] Function Syntax Help

[Pavel Stehule]

Hello

You are using PLpgSQL CASE statement

this start by CASE keyword and finishing by END CASE keywords

CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
CASE
WHEN NEW.period =  201001
THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
END;
RETURN NULL;
END CASE; -- <
$$ LANGUAGE plpgsql;




2014-06-26 0:19 GMT+02:00 Dennis Ryan :

> I having trouble with correct syntax to get this trigger function to
> compile.  I have tried every combination of removing the ‘;’ characters but
> the function will not compile.  Can someone tell me what I am doing wrong,
> I am stumped. I will be adding addition when clauses the case statement
> once I get this simplified version to compile.
>
> I am using 9.3.4, both postgres and psql.
>
>
> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
> CASE
> WHEN NEW.period =  201001
> THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
> END;
> RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
>
> ERROR:  syntax error at or near ";"
> LINE 7: END;
>

Re: [GENERAL] Function Syntax Help

[Shaun Thomas]

On 06/25/2014 05:19 PM, Dennis Ryan wrote:


 CASE
 WHEN NEW.period =  201001
 THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
 END;


You can't just have a bare CASE statement in plpgsql. Try this:

CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.period = 201001 THEN
INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] Function Syntax Help

[Raymond O'Donnell]

On 26/06/2014 17:26, Raymond O'Donnell wrote:
> On 25/06/2014 23:19, Dennis Ryan wrote:
>> I having trouble with correct syntax to get this trigger function to
>> compile.  I have tried every combination of removing the ‘;’ characters
>> but the function will not compile.  Can someone tell me what I am doing
>> wrong, I am stumped. I will be adding addition when clauses the case
>> statement once I get this simplified version to compile.
>>
>> I am using 9.3.4, both postgres and psql.
>>
>>
>> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
>> RETURNS TRIGGER AS $$
>> BEGIN
>> CASE
>> WHEN NEW.period =  201001
>> THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
> 
> The problem is the semi-colon after (NEW.*). There isn't one inside a
> CASE construct.

Whoops - Pavel is of course correct - this is a pl/pgsql CASE, not an
SQL one. Sorry - my mistake.

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] Function Syntax Help

[Pavel Stehule]

2014-06-26 18:28 GMT+02:00 Shaun Thomas :

> On 06/25/2014 05:19 PM, Dennis Ryan wrote:
>
>   CASE
>>  WHEN NEW.period =  201001
>>  THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
>>  END;
>>
>
> You can't just have a bare CASE statement in plpgsql. Try this:
>
>
> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
> IF NEW.period = 201001 THEN
>
> INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
> END IF;
>
> RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>


or use a plpgsql case
http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

Regards

Pavel


>
> --
> Shaun Thomas
> OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> stho...@optionshouse.com
>
> __
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions
> related to this email
>
>
> --
> 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] Function Syntax Help

[Pavel Stehule]

2014-06-26 18:26 GMT+02:00 Raymond O'Donnell :

> On 25/06/2014 23:19, Dennis Ryan wrote:
> > I having trouble with correct syntax to get this trigger function to
> > compile.  I have tried every combination of removing the ‘;’ characters
> > but the function will not compile.  Can someone tell me what I am doing
> > wrong, I am stumped. I will be adding addition when clauses the case
> > statement once I get this simplified version to compile.
> >
> > I am using 9.3.4, both postgres and psql.
> >
> >
> > CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
> > RETURNS TRIGGER AS $$
> > BEGIN
> > CASE
> > WHEN NEW.period =  201001
> > THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);
>
> The problem is the semi-colon after (NEW.*). There isn't one inside a
> CASE construct.
>

no, using INSERT inside SQL CASE is not possible (with or without
semicolon), but inside PL/pgSQL it is ok. But PL/pgSQL statement uses END
CASE like others END .. END IF, END LOOP,

Regards

Pavel


>
> Ray.
>
>
> > END;
> > RETURN NULL;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> >
> > ERROR:  syntax error at or near ";"
> > LINE 7: END;
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>
>
> --
> 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] Alternative to psql -c ?

[Tom Lane]

James Le Cuirot  writes:
> This got me wondering what Rails uses. I dug into ActiveRecord and
> found that apart from the odd call to PQexec with hardcoded single
> statements, it uses PQsendQuery. The libpq docs state a few of the
> differences but don't mention whether PQsendQuery automatically creates
> a transaction like PQexec does. Please could you clarify this?

PG is not capable of executing queries that are not in transactions,
so yes, PQsendQuery will create a single-statement transaction if you
haven't sent BEGIN.  However, there's a huge difference for the
purposes we're discussing here: PQsendQuery does not allow more than
one SQL command in the string.  So most of this discussion is irrelevant
when you're going through that API.

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] python modul pre-import to avoid importing each time

[Jeff Janes]

On Thu, Jun 26, 2014 at 2:14 AM, Rémi Cura  wrote:
> Hey,
> thanks for your answer !
>
> Yep you are right, the function I would like to test are going to be called
> a lot (100k times), so even 15 ms per call matters.
>
> I'm still a bit confused by a topic I found here :
> http://stackoverflow.com/questions/15023080/how-are-import-statements-in-plpython-handled
> The answer gives a trick to avoid importing each time, so somehow it must be
> usefull.

I'd want to see the benchmark before deciding that how useful it actually is

Anyway, that seems to be about calling import over and over within the
same connection, not between different connections, as is your issue.
Also, I think that that suggestion is targeted at removing what is
already a very minor overhead, which is importing the symbols from the
module into the importer's namespace (or however you translate that
into python speak).  The slow part is loading the module in the first
place (finding the shared objects, parsing the module's source code,
gluing them together, etc.), not importing the python symbols.

If you arrange to re-use connections, you will probably find no
further optimization is needed.


> On another internet page (can't find it anymore) somebody mentioned this
> module loading at server startup, one way or another, but gave no precision.
> It seems that the "plpy" python module get loaded by default, would'nt it be
> possible to hack this module to add other import inside it?

I just thought your question looked lonely and that I'd tell you what
I learned about plperl in case it helped.  There may be a way to do
about the same thing in plpython, but if so it doesn't seem to be
documented, or analogous to the way plperl does it.  I'm afraid that
exhausts my knowledge of plpython.  I don't see any files that
suggests there is a user-editable plpy.py module.  If you are willing
to monkey around with C and recompiling, you could probably make it
happen somehow, though.

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] python modul pre-import to avoid importing each time

[Tom Lane]

Adrian Klaver  writes:
> On 06/26/2014 02:14 AM, Rémi Cura wrote:
>> On another internet page (can't find it anymore) somebody mentioned this
>> module loading at server startup, one way or another, but gave no
>> precision. It seems that the "plpy" python module get loaded by default,
>> would'nt it be possible to hack this module to add other import inside it?

> In a sense that is what is being suggested above.

IIRC, plperl has a GUC you can set to tell it to do things at the time
it's loaded (which of course you use in combination with having listed
plperl in shared_preload_libraries).  There's no reason except lack of
round tuits why plpython couldn't have a similar feature.

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] Function Syntax Help

[Raymond O'Donnell]

On 25/06/2014 23:19, Dennis Ryan wrote:
> I having trouble with correct syntax to get this trigger function to
> compile.  I have tried every combination of removing the ‘;’ characters
> but the function will not compile.  Can someone tell me what I am doing
> wrong, I am stumped. I will be adding addition when clauses the case
> statement once I get this simplified version to compile.
> 
> I am using 9.3.4, both postgres and psql.
> 
> 
> CREATE OR REPLACE FUNCTION sn_dm_b.pm_insert_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
> CASE
> WHEN NEW.period =  201001
> THEN INSERT INTO sn_dm_b.pm201001 VALUES (NEW.*);

The problem is the semi-colon after (NEW.*). There isn't one inside a
CASE construct.

Ray.


> END;
> RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
> 
> 
> ERROR:  syntax error at or near ";"
> LINE 7: END;


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

[GENERAL] Repeated semop calls

[Anand Kumar, Karthik]

Hi,

We run postgres 9.3.3 on Centos 6.3, kernel 2.6.32-431.3.1. Every once in a 
while, we see postgres processes spinning on semop:

Here is an output from an strace on a delete process:

root@site-db01a:~ # strace -p 53744
Process 53744 attached - interrupt to quit
semop(21692498, {{6, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20480045, {{15, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21135425, {{14, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21692498, {{6, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21266501, {{4, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21692498, {{6, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20250662, {{0, 1, 0}}, 1) = 0
semop(21135425, {{14, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20250662, {{0, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21266501, {{6, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(20250662, {{0, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21626960, {{7, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21266501, {{4, 1, 0}}, 1) = 0
semop(21266501, {{4, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20119586, {{5, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21200963, {{5, 1, 0}}, 1) = 0
semop(21135425, {{14, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21233732, {{9, 1, 0}}, 1) = 0
semop(21266501, {{7, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21266501, {{4, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21135425, {{14, 1, 0}}, 1)= 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21233732, {{9, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20774966, {{7, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21233732, {{9, 1, 0}}, 1) = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(20185124, {{3, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
…
(output truncated)
…
semop(21233732, {{9, 1, 0}}, 1) = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21168194, {{10, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21168194, {{10, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21168194, {{10, 1, 0}}, 1)= 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21561422, {{12, -1, 0}}, 1)   = 0
semop(21168194, {{10, 1, 0}}, 1)= 0
sendto(3, "<134>Jun 26 12:23:30 postgres[53"..., 495, MSG_NOSIGNAL, NULL, 0) = 
495
sendto(3, "<134>Jun 26 12:23:30 postgres[53"..., 258, MSG_NOSIGNAL, NULL, 0) = 
258
sendto(10, 
"\2\0\0\\2\0\0001@\0\0\5\0\0\0\17\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 560, 
0, NULL, 0) = 560
sendto(11, "1\0\0\0\0042\0\0\0\4n\0\0\0\4C\0\0\0\rDELETE 0\0Z\0\0"..., 35, 0, 
NULL, 0) = 35
recvfrom(11, "P\0\0\1\220\0delete from sym_data where"..., 8192, 0, NULL, NULL) 
= 576
lseek(27, 0, SEEK_END)

Re: [GENERAL] Questions about daterange() function

[Ken Tanzer]

>
> > So here are my questions:
> >
> > 1)  Is there anyway to control this behavior of daterange(), or is it
> just
> > best to (for example) add 1 to the upper bound argument if I want an
> > inclusive upper bound?
>
> See link for question #3; namely use the three-arg version of daterange
> (type,type,text)
>
>
> > 2)  This is purely cosmetic, but is there anyway to control the output
> > formatting of a daterange to show the upper bound as inclusive?  So that
> > daterange(d1,d2) would display as [d1,d2-1] rather than [d1,d2)?
>
> Not easily - you could write a custom type with the desired canonical form.
>
>
> > 3)  I couldn't find this discussed in the documentation, and specifically
> > didn't find the daterange() function documented, including on this page
> > where I might have expected it:
> > http://www.postgresql.org/docs/9.3/static/functions-range.html.  Is it
> > somewhere else where I'm not finding it?
>
> Yes, the documentation could maybe use some work on this topic.  The
> relevant information is provided at:
>
> http://www.postgresql.org/docs/9.3/interactive/rangetypes.html
>
> See especially: 8.17.2 & 8.17.6
>
> David J.
>
>
> Thanks for your help!



>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Questions-about-daterange-function-tp5809274p5809277.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
>



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] DATA corruption after promoting slave to master

[Karthik Iyer]

A full dump and restore would definitely help. I tend not to suggest 
that often because I work with very large databases that are usually 
extremely cumbersome to dump and restore.


But yeah, if you can get a successful pg_dump from your database, a 
restore should obviously clean up all of your data and index 
inconsistencies if you're willing to wait. 


Thanks a lot Shaun. Appreciate the help.

- Karthik


--
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] Alternative to psql -c ?

[James Le Cuirot]

On Thu, 26 Jun 2014 11:02:09 -0700
Tom Lane  wrote:

> James Le Cuirot  writes:
> > This got me wondering what Rails uses. I dug into ActiveRecord and
> > found that apart from the odd call to PQexec with hardcoded single
> > statements, it uses PQsendQuery. The libpq docs state a few of the
> > differences but don't mention whether PQsendQuery automatically
> > creates a transaction like PQexec does. Please could you clarify
> > this?
> 
> PG is not capable of executing queries that are not in transactions,
> so yes, PQsendQuery will create a single-statement transaction if you
> haven't sent BEGIN.  However, there's a huge difference for the
> purposes we're discussing here: PQsendQuery does not allow more than
> one SQL command in the string.  So most of this discussion is
> irrelevant when you're going through that API.

Heh, are you sure? From the docs...

"Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a
command string contains multiple SQL commands, the results of those
commands can be obtained individually."

I also seem to be able to execute multiple statements at a time through
ActiveRecord. This method is just a thin wrapper around PQsendQuery.
Granted I only get the last result but if I change the first statement
to something erroneous, it does fail.

1.9.1 :001 > ActiveRecord::Base.connection.execute("SELECT 1; SELECT 2").first
 => {"?column?"=>"2"}

Regards,
James


-- 
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] Alternative to psql -c ?

[Tom Lane]

James Le Cuirot  writes:
> Tom Lane  wrote:
>> PG is not capable of executing queries that are not in transactions,
>> so yes, PQsendQuery will create a single-statement transaction if you
>> haven't sent BEGIN.  However, there's a huge difference for the
>> purposes we're discussing here: PQsendQuery does not allow more than
>> one SQL command in the string.  So most of this discussion is
>> irrelevant when you're going through that API.

> Heh, are you sure? From the docs...

> "Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a
> command string contains multiple SQL commands, the results of those
> commands can be obtained individually."

Oh, sorry, I was confusing that with the extended-query-mode API
(PQexecParams).

Yeah, PQsendQuery is like PQexec for this purpose --- the backend does
not actually know the difference.

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] Fast data, slow data

[Tim Uckun]

This is what I was thinking but I am worried about two things.

1. If there is a very large set of data in the table that needs to be moved
this will be slow and might throw locks which would impact the performance
of the inserts and the updates.
2. Constantly deleting large chunks of data might cause vacuum problems.


On Fri, Jun 27, 2014 at 2:49 AM, Shaun Thomas 
wrote:

> On 06/26/2014 04:29 AM, Tim Uckun wrote:
>
>  I don't think partitioning is a good idea in this case because the
>> partitions will be for small time periods (5 to 15 minutes).
>>
>
> Actually, partitioning might be exactly what you want, but not in the way
> you might think. What you've run into is actually a pretty common usage
> pattern. How we solve problems like this where I work is to use table
> inheritance alone. Consider this:
>
> CREATE TABLE my_table
> (
>   ... columns
> );
>
> CREATE TABLE my_table_stable (INHERITS my_table);
>
> Then you create a job that runs however often you want, and all that job
> does, is move old rows from my_table, to my_table_stable. Like so:
>
> BEGIN;
>
> INSERT INTO my_table_stable
> SELECT * FROM ONLY my_table
>  WHERE date_col >= now() - INTERVAL '15 minutes';
>
> DELETE FROM ONLY my_table
>  WHERE date_col >= now() - INTERVAL '15 minutes';
>
> COMMIT;
>
> Or whatever. But you get the idea.
>
> This way, you still get all the data by selecting from my_table, but the
> data is partitioned in such a way that you can put the high turnover table
> in another tablespace, or otherwise modify it for performance reasons.
>
> --
> Shaun Thomas
> OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> stho...@optionshouse.com
>
> __
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions
> related to this email
>

Re: [GENERAL] Fast data, slow data

[Tim Uckun]

The database is functioning fine now but I am anticipating a much higher
workload in the future.  The table in question is probably going to have a
few million rows per day inserted into it when it gets busy, if it gets
very busy it might be in the tens of millions per day but that's
speculation at this point.

I don't want to say that the data is not important but if I drop one or two
sensor readings it's not going to be the end of the world.



On Fri, Jun 27, 2014 at 3:45 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> On 06/26/2014 02:29 AM, Tim Uckun wrote:
>
>> I have a use case in which the most recent data experiences a lot of
>> transactions (inserts and updates) and then the churn kind of calms down.
>>  Eventually the data is relatively static and will only be updated in
>> special and sporatic events.
>>
>> I was thinking about keeping the high churn data in a different table so
>> that the vacuums on that table can go faster and the vacuums on the rest of
>> the data will rarely be needed.
>>
>> Ideally the high churn data would be in an in memory table but failing
>> that I thought that an unlogged table would be good enough.  So now I need
>> a way to flush the oldest data in this table to another logged table and do
>> it in an efficient manner. I don't think partitioning is a good idea in
>> this case because the partitions will be for small time periods (5 to 15
>> minutes).
>>
>> Anybody have a similar problem? If so how did you solve it?
>>
>
> It's not at all unusual for a database to experience a high portion of its
> activity on a small subset of the data so in that sense the "problem" is
> already solved by appropriate sizing and tuning to make appropriate
> adjustments to the caching and other properties already provided by the OS
> and DB.
>
> Having said that, there is far too little information here to provide
> specific advice on tuning and other approaches that may be of use for you.
> In particular:
>
> Is this currently a hypothetical question or do you have a running system
> that is experiencing performance problems?
>
> In either case can you provide some parameters including rows and size of
> your large table(s), what you mean by "high churn" (rate of inserts,
> deletes, updates and how they are grouped into transactions), the size of
> the table that is heavily updated, how the database is used (transactional,
> data-mining, ...) and anything else you feel might help the list understand
> your use-case.
>
> If you have a live database, any metrics/observations you can supply might
> be helpful. For example, are particular queries slow? What speed is
> required? Do you have lots of simultaneous connections or is everything
> through a single connection.
>
> Finally,  confirm that the data is not important or is easily recreated
> (implied by your in-memory/unlogged-table comments).
>
> Cheers,
> Steve
>
>
>

Re: [GENERAL] python modul pre-import to avoid importing each time

[Adrian Klaver]

On 06/26/2014 02:14 AM, Rémi Cura wrote:

Hey,
thanks for your answer !

Yep you are right, the function I would like to test are going to be
called a lot (100k times), so even 15 ms per call matters.



I got to thinking about this.

100K over what time frame?

How is it being called?




--
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

[GENERAL] Executing nodenestloop.c

[Ravi Kiran]

hi,

I am using the environment Eclipse for the execution of the programs in the
executor, whenever I give break points to specific program in eclipse , the
control goes to main.c and finally the process never comes back to the
actual program.
Is there any way that the process be constrained only to my specific
program.

Thank you

Re: [GENERAL] Executing nodenestloop.c

[Atri Sharma]

On Fri, Jun 27, 2014 at 11:53 AM, Ravi Kiran 
wrote:

> hi,
>
> I am using the environment Eclipse for the execution of the programs in
> the executor, whenever I give break points to specific program in eclipse ,
> the control goes to main.c and finally the process never comes back to the
> actual program.
> Is there any way that the process be constrained only to my specific
> program.
>
> Thank you
>

If you are attaching GDB to your running postgres service, the breakpoints
you set will be reached only when the query you execute actually touches
those parts of code. Make sure that the query you execute logically touches
your breakpoints.

Regards,

Atri

-- 
Regards,

Atri
*l'apprenant*