On Thu, Jun 11, 2009 at 3:27 AM, Josh Berkus wrote:
> On 6/10/09 7:20 PM, Christophe wrote:
>>
>> On Jun 10, 2009, at 6:27 PM, Chris wrote:
>>>
>>> Can you list the filesizes on http://media.postgresql.org/sfpug/ please?
>
> Oh, good idea. Didn't think of it.
Please move the index page to the wik
Hi there.
When is a good time to use cascading deletes?
Usually, for safety reasons, I prefer to not ever use cascading
deletes. But that can lead to some complex code that uses topological
sorts etc to manually delete records in the right order, when a
cascading delete is needed.
Ideally, I'd l
The following code works to update a table in order with a sequence.
I have tried it on a number of different types of fields.
create temporary sequence seq_1;
update tbl1 set currentsort=nextval('seq_1')
from (select tbl1id from tbl1 order by tbl1id) b
where tbl1.tbl1id=b.tbl1id;
drop sequence
Hi,
Here's my general situation: I have a function which takes in an optional
ARRAY of Ids as so:
RETURN QUERY SELECT * FROM a WHERE a.id = ANY(v_ids) or v_ids is null;
However it seems that the ...or v_ids is null... bit forces a sequential
scan on a. Reading this list, it seems the best wa
Hello
8.4 suport USING clause - so there is possible use array variable directly.
regards
Pavel Stehule
2009/6/11 Shakil Shaikh :
> Hi,
>
> Here's my general situation: I have a function which takes in an optional
> ARRAY of Ids as so:
>
> RETURN QUERY SELECT * FROM a WHERE a.id = ANY(v_ids) or
On Jun 11, 2009, at 10:59 AM, David wrote:
Hi there.
When is a good time to use cascading deletes?
As a general rule of thumb I use cascading deletes if the data in a
record is meaningless without the record that the foreign key points to.
Ideally, I'd like postgresql to not do cascading
Re added list!
--
Sent: Thursday, June 11, 2009 11:04 AM
To: "Shakil Shaikh" <>
Subject: Re: [GENERAL] Array Parameters in EXECUTE
Here's my general situation: I have a function which takes in an
optional ARRAY of Ids as so:
RETURN QUERY SELECT *
On Wed, Jun 10, 2009 at 04:51:44PM -0400, Tom Lane wrote:
> It's the new implementation. Depending on unspecified implementation
> details is a good way to have broken code.
i'm not sure if it's good change. there might be perfectly good reasons
to increment idx from within loop.
Best regards,
the openstreetmap project (http://osm.org/) recently moved from using
mysql to postgres and we're trying to improve some of our tools using
the new functionality that postgres provides.
in particular, we are dumping changes to the database at short
intervals (currently every minute, hour and day [
Thanks for the tips, those make sense.
I was thinking through this some more after sending my mail, and came
to similar conclusions.
It would be nice though if this info was more available to people
doing research on the subject. Where did you pick up these ideas? At
least this thread should star
2009/6/11 hubert depesz lubaczewski :
> On Wed, Jun 10, 2009 at 04:51:44PM -0400, Tom Lane wrote:
>> It's the new implementation. Depending on unspecified implementation
>> details is a good way to have broken code.
>
> i'm not sure if it's good change. there might be perfectly good reasons
> to i
On Thu, Jun 11, 2009 at 12:45:56PM +0200, Pavel Stehule wrote:
> generally - modification of cycle's control variable isn't good
> technique, because it's should be broken by some optimizations. When
i would argue then that these optimizations are broken, then.
> you would to modify this some var
On Thu, Jun 11, 2009 at 11:25 AM, Matt Amos wrote:
>
> what problems are we going to create for ourselves if we create a
> btree index on xmin casted to int4? would it be as efficient to use a
> hash index, create a temporary table of txids that we're querying with
> a hash index and do an explicit
On 6/11/09, Matt Amos wrote:
> the openstreetmap project (http://osm.org/) recently moved from using
> mysql to postgres and we're trying to improve some of our tools using
> the new functionality that postgres provides.
>
> in particular, we are dumping changes to the database at short
> inte
David wrote:
Thanks for the tips, those make sense.
I was thinking through this some more after sending my mail, and came
to similar conclusions.
It would be nice though if this info was more available to people
doing research on the subject. Where did you pick up these ideas? At
least this thr
> Here's my general situation: I have a function which takes in an
> optional ARRAY of Ids as so:
>
> RETURN QUERY SELECT * FROM a WHERE a.id = ANY(v_ids) or v_ids is null;
>
If I understand what you are trying to do, if your passed in parameter
is null then select * and if there is something t
I use cascading deletes as per business rule.
For example, my customer record has multiple orders and each order can
have multiple shipments and multiple payments.
My business rule is not to erase a customer with orders, but orders
should be erased even if they have shipments or payments.
The bus
On Thu, Jun 11, 2009 at 9:59 AM, David wrote:
>
> Ideally, I'd like postgresql to not do cascading deletes, *except*
> when I tell it to, and the rest of the time fail when the user didn't
> explicitly "opt in" for cascading deletes. When it comes to enabling
> cascading deletes, I don't really lik
Marko Kreen wrote:
4-byte xids on btree may create data corruption.
Can you be more specific on this? I'm aware of xid being an unsigned
integer which means we need to deal with the cast resulting in negative
numbers. This means we have to split our range queries into several
pieces when
2009/6/11 hubert depesz lubaczewski :
> On Thu, Jun 11, 2009 at 12:45:56PM +0200, Pavel Stehule wrote:
>> generally - modification of cycle's control variable isn't good
>> technique, because it's should be broken by some optimizations. When
>
> i would argue then that these optimizations are broke
On Thu, Jun 11, 2009 at 12:59 PM, Brett Henderson wrote:
> I have a couple of hesitations with using this approach:
> 1. We can only run the replicator once.
> 2. We can only run a single replicator.
> 3. It requires write access to the db.
>
> 1 is perhaps the biggest issue. It means that we only
I've been working with Matt on this. Thanks for the suggestions.
Greg Stark wrote:
On Thu, Jun 11, 2009 at 11:25 AM, Matt Amos wrote:
what problems are we going to create for ourselves if we create a
btree index on xmin casted to int4? would it be as efficient to use a
hash index, create a
On Thu, Jun 11, 2009 at 12:59 PM, Brett Henderson wrote:
> Greg Stark wrote:
>> Another option to consider would be including a boolean column
>> "dumped" defaulted to false. Then you could have a partial index on
>> the primary key or date "WHERE NOT dumped". Then when you dump you can
>> "SELECT
On Thu, Jun 11, 2009 at 1:13 PM, Brett Henderson wrote:
> Marko Kreen wrote:
> Sorry, I'm not sure what you're suggesting with txid_current(). We're
> currently using the |txid_current_snapshot|() method which returns us the
> maximum transaction id plus in-flight transactions. We specifically ex
Vyacheslav Kalinin writes:
> $conn = pg_pconnect("dbname=foo");
Please reconsider and use plain pg_connect().
--
dim
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On June 11, 2009 01:21:09 am Albe Laurenz wrote:
> Phil wrote:
> > I want to develop an app which uses libpq, built with mingw.
> > Is there a download package which contains just the include files/dlls?
> > If not, what package do I download? I don't need the server, just the
> > client libraries.
> Vyacheslav Kalinin writes:
>
>> $conn = pg_pconnect("dbname=foo");
>
> Please reconsider and use plain pg_connect().
Would you like to elaborate on that? Why connect and not pconnect?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On June 10, 2009 10:00:48 pm Andy Colson wrote:
> Phil Longstaff wrote:
> > I want to develop an app which uses libpq, built with mingw. Is there a
> > download package which contains just the include files/dlls? If not,
> > what package do I download? I don't need the server, just the client
> > l
On Thu, Jun 11, 2009 at 4:36 PM, Dimitri Fontaine wrote:
> Vyacheslav Kalinin writes:
>
> > $conn = pg_pconnect("dbname=foo");
>
> Please reconsider and use plain pg_connect().
> --
> dim
>
Uh, I just copied/pasted that part from somewhere in PHP manual, personally
I tend to use plain pg_connect
On 6/11/09, Brett Henderson wrote:
> Marko Kreen wrote:
>
> > 4-byte xids on btree may create data corruption.
> >
> >
> Can you be more specific on this? I'm aware of xid being an unsigned
> integer which means we need to deal with the cast resulting in negative
> numbers. This means we have t
Phil Longstaff wrote:
On June 10, 2009 10:00:48 pm Andy Colson wrote:
> Phil Longstaff wrote:
> > I want to develop an app which uses libpq, built with mingw. Is there a
> > download package which contains just the include files/dlls? If not,
> > what package do I download? I don't need the s
On 6/11/09, Matt Amos wrote:
> On Thu, Jun 11, 2009 at 1:13 PM, Brett Henderson wrote:
> >> See pgq.batch_event_sql() function in Skytools [2] for how to
> >> query txids between snapshots efficiently and without being affected
> >> by long transactions.
> >
> > I'll take a look.
>
> it was l
On Wed, Jun 10, 2009 at 12:49 PM, Gus
Gutoski wrote:
> Of course, the double minus sign comments out the rest of the line and
> the statement is left dangling, looking for a terminating semicolon.
SQL statements are not terminated with semi-colons. The semi-colon is
used in the psql shell to indi
I need the include files as well so I can build against the library.
Phil
From: Andy Colson
To: Phil Longstaff
Cc: pgsql-general@postgresql.org
Sent: Thursday, June 11, 2009 9:47:52 AM
Subject: Re: [GENERAL] Libpq on windows
Phil Longstaff wrote:
> On June 10
Phil Longstaff wrote:
On June 11, 2009 01:21:09 am Albe Laurenz wrote:
> Phil wrote:
> > I want to develop an app which uses libpq, built with mingw.
> > Is there a download package which contains just the include files/dlls?
> > If not, what package do I download? I don't need the server, ju
My stuff is in Delphi, so I don't use the .h files, I'm not even sure
which one's you'd need.
You should grab the source from the main site, it'll have all the .h
files you'll ever need.
-Andy
Phil Longstaff wrote:
I need the include files as well so I can build against the library.
Phil
On Wed, Jun 10, 2009 at 12:49 PM, Gus
Gutoski wrote:
> Hi,
>
> I'm a noob who failed to properly sanitize incoming data from the
> front end. As a result, a poor hapless user managed to smuggle in a
> malicious UPDATE statement that corrupted every single record in a
> 7+ table. Only 3 fields
On Mon, Jun 08, 2009 at 08:08:02PM +0100, Oliver Kohll - Mailing Lists wrote:
> On 8 Jun 2009, at 17:23, Merlin Moncure wrote:
>
Is there a way when creating a table to limit it to one row? That
is,
without using a stored procedure?
I searched the documentation, but did
On Thu, Jun 11, 2009 at 4:44 AM, Greg Stark wrote:
> I generally leave cascade off except for many-to-many mapping tables
> which contain no additional data and are a pain to manage. Which does
> sound similar to Alban's rule of thumb.
Cascading deletes also make sense for vertically partitioned
Using Postgres 8.3.6, trying to run a query with
bound parameters gives me:
ERROR: XX000: error from Perl function
"expire_facts": operator does not exist: retire
d_date => retired_date at line 56.
"retired_date" is one of a set of domains defined via:
create domain publish_d
Steven Lembark writes:
> Using Postgres 8.3.6, trying to run a query with
> bound parameters gives me:
> ERROR: XX000: error from Perl function
> "expire_facts": operator does not exist: retire
> d_date => retired_date at line 56.
Uh, maybe you are looking for ">=" rather than "=>
Hi
Does postgresql have any build-in auditing features like in Oracle's
total-recall or fine grained auditing
http://www.oracle.com/technology/products/database/oracle11g/pdf/total-recall-datasheet.pdf
http://www.oracle.com/technology/deploy/security/database-security/fine-grained-audit
On Thu, Jun 11, 2009 at 2:48 PM, Marko Kreen wrote:
> On 6/11/09, Matt Amos wrote:
>> On Thu, Jun 11, 2009 at 1:13 PM, Brett Henderson wrote:
>> >> See pgq.batch_event_sql() function in Skytools [2] for how to
>> >> query txids between snapshots efficiently and without being affected
>> >> by l
My rule of thumb for when to use to not use cascading deletes is this:
If the what the record represents can essentially be thought of a "part
of" what the record that it references represents, I use cascading
deletes. If what the record represents has an independent existence,
that it, it does no
You get what you ask for :-)
You can use the function array-to-string with a comma as the separator.
The array should look like '{val1,val2,val3}' in text format.
Sim
Shakil Shaikh wrote:
> Re added list!
>
> --
> Sent: Thursday, June 11, 2009 11:0
Dave,
Please move the index page to the wiki or pugs site. The media server
is *only* supposed to host the files - we don't want any HTML content
on there.
Oh, ok. I'll put a placeholder page redirecting people.
However ... there is the streaming page. That needs to stay somewhere
we have
On Thu, 2009-06-11 at 11:13 -0700, Josh Berkus wrote:
> Dave,
>
> > Please move the index page to the wiki or pugs site. The media server
> > is *only* supposed to host the files - we don't want any HTML content
> > on there.
>
> Oh, ok. I'll put a placeholder page redirecting people.
>
> Howev
On 6/11/09, Matt Amos wrote:
> On Thu, Jun 11, 2009 at 2:48 PM, Marko Kreen wrote:
> > On 6/11/09, Matt Amos wrote:
> >> On Thu, Jun 11, 2009 at 1:13 PM, Brett Henderson wrote:
> >> >> See pgq.batch_event_sql() function in Skytools [2] for how to
> >> >> query txids between snapshots effici
On Thu, Jun 11, 2009 at 7:13 PM, Josh Berkus wrote:
> Dave,
>
>> Please move the index page to the wiki or pugs site. The media server
>> is *only* supposed to host the files - we don't want any HTML content
>> on there.
>
> Oh, ok. I'll put a placeholder page redirecting people.
Thanks.
> Howev
Josh, Dave,
Can you embed the files on the PUGs page like JD suggests? What I want
to avoid is an ad-hoc website springing up on media.postgresql.org
that ends up in Google and being linked from who-knows-where.
Hmmm ... not sure my Drupal-foo is good enough. Will seek help
--
Josh Berk
Thanks for the replies.
Tom Lane wrote:
> This being 8.1, if you haven't turned on autovacuum there is some chance
> of that.
Unfortunately, autovacuum was on. I don't recall ever turning it on,
but this database is over two years old; it's possible that I blindly
followed advice from pgAdmin or
Albe Laurenz *EXTERN* wrote:
>
> stevesub wrote:
>> I keep having this need to create a function that will change the row
>> data
>> as I am walking through the data. For example, I process each row in
>> order,
>> if column1 change from previous row, set column2 to true.
>>
>> Is this possibl
> It's a classic story. I'm volunteering about one day per month for
> this project, learning SQL as I go. Priority was always given to the
> "get it working" tasks and never the "make it safe" tasks. I had/have
> grandiose plans to rewrite the whole system properly after I graduate.
> Unfort
On Thu, 2009-06-11 at 11:53 -0700, Josh Berkus wrote:
> Josh, Dave,
>
> > Can you embed the files on the PUGs page like JD suggests? What I want
> > to avoid is an ad-hoc website springing up on media.postgresql.org
> > that ends up in Google and being linked from who-knows-where.
>
> Hmmm ... no
Something interesting I've noticed. If I have a table by the same
name in two different schemas, say public and foo, and my search path
is set to 'public, foo', \d without an argument lists only the one in
public.
I see why from the SQL that \d generates, but just wondering why \d
doesn't generat
On 06/11/2009 11:33 AM, Eric Schwarzenbach wrote:
My rule of thumb for when to use to not use cascading deletes is this:
If the what the record represents can essentially be thought of a "part
of" what the record that it references represents, I use cascading
deletes. If what the record represen
Brandon Metcalf writes:
> Something interesting I've noticed. If I have a table by the same
> name in two different schemas, say public and foo, and my search path
> is set to 'public, foo', \d without an argument lists only the one in
> public.
That's intentional. It's designed to show the sam
Given a datetime column, not null, is there a single syntax that
permits searching for all dates in a given year, year+month, and
year+month+day such that a single parameterised query can handle all
three circumstances?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne
On Jun 11, 2009, at 1:23 PM, James B. Byrne wrote:
Given a datetime column, not null, is there a single syntax that
permits searching for all dates in a given year, year+month, and
year+month+day such that a single parameterised query can handle all
three circumstances?
Well, of course, in a
"James B. Byrne" writes:
> Given a datetime column, not null, is there a single syntax that
> permits searching for all dates in a given year, year+month, and
> year+month+day such that a single parameterised query can handle all
> three circumstances?
Try date_trunc() ... however, if you want th
t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > Something interesting I've noticed. If I have a table by the same
t> > name in two different schemas, say public and foo, and my search path
t> > is set to 'public, foo', \d without an argument lists only the one in
t> > public
On 11/06/2009 21:39, Brandon Metcalf wrote:
> Is there a "\" command to show all tables in the current search path?
\dt
\? is your friend
Ray.
--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galw
r == r...@iol.ie writes:
r> On 11/06/2009 21:39, Brandon Metcalf wrote:
r> > Is there a "\" command to show all tables in the current search path?
r> \dt
r> \? is your friend
Nope. You didn't read the entire thread. If you do, you'll see why
\dt isn't the answer.
I'm well aware of
On Thu, Jun 11, 2009 at 2:35 PM, Tom Lane wrote:
> "James B. Byrne" writes:
>> Given a datetime column, not null, is there a single syntax that
>> permits searching for all dates in a given year, year+month, and
>> year+month+day such that a single parameterised query can handle all
>> three circu
Brandon Metcalf writes:
> Is there a "\" command to show all tables in the current search path?
Even ones that are masked by earlier search_path entries? No.
You could craft some manual query on pg_class, no doubt.
regards, tom lane
--
Sent via pgsql-general mailing li
t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > Is there a "\" command to show all tables in the current search path?
t> Even ones that are masked by earlier search_path entries? No.
Correct. Just wondering if there was something undocumented :)
t> You could craft some ma
On Thu, 11 Jun 2009, SHARMILA JOTHIRAJAH wrote:
Does postgresql have any build-in auditing features like in Oracle's
total-recall or fine grained auditing
There's nothing built-in, but it's not too difficult to build such
facilities yourself. I'm quite sure you could find someone who would
On Thu, Jun 11, 2009 at 1:32 PM, Chris Spotts wrote:
>
>> It's a classic story. I'm volunteering about one day per month for
>> this project, learning SQL as I go. Priority was always given to the
>> "get it working" tasks and never the "make it safe" tasks. I had/have
>> grandiose plans to rewr
James B. Byrne wrote:
Given a datetime column, not null, is there a single syntax that
permits searching for all dates in a given year, year+month, and
year+month+day such that a single parameterised query can handle all
three circumstances?
That's a little vague, so how about:
select * from
Greg Smith wrote:
> On Thu, 11 Jun 2009, SHARMILA JOTHIRAJAH wrote:
>
>> Does postgresql have any build-in auditing features like in Oracle's
>> total-recall or fine grained auditing
>
> There's nothing built-in, but it's not too difficult to build such
> facilities yourself.
I have added this
On Thu, 11 Jun 2009, Brandon Metcalf wrote:
Is there a "\" command to show all tables in the current search path?
SELECT
nspname,relname,relkind
FROM
pg_class c,
pg_namespace n
WHERE
c.relnamespace = n.oid AND
relkind='r' AND
nspname !~ '^pg_toast' AND
nspname = ANY(current_schem
On Thursday 11. June 2009, David wrote:
>When is a good time to use cascading deletes?
As a real world example, I've got a data model that consists of three
major entities: Persons, Events, and Sources. The Events table is
linked to Persons through the junction table Participants, and to the
S
On Thursday 11. June 2009, James B. Byrne wrote:
>Given a datetime column, not null, is there a single syntax that
>permits searching for all dates in a given year, year+month, and
>year+month+day such that a single parameterised query can handle all
>three circumstances?
Apart from the other exce
Is there a way through the libpq interface to access performance data on
a query?
I don't see an obvious way to do it - that is, retrieve the amount of
time (clock, cpu, etc) required to process a command or query, etc
Thanks in advance!
--
--
Karl Denninger
k...@denninger.net
begin:vc
74 matches
Mail list logo
