On Thu, Jun 11, 2009 at 3:27 AM, Josh Berkusj...@agliodbs.com 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
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
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
Hello
8.4 suport USING clause - so there is possible use array variable directly.
regards
Pavel Stehule
2009/6/11 Shakil Shaikh ssha...@hotmail.com:
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 =
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
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
2009/6/11 hubert depesz lubaczewski dep...@depesz.com:
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
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
On Thu, Jun 11, 2009 at 11:25 AM, Matt Amoszerebub...@gmail.com 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
On 6/11/09, Matt Amos zerebub...@gmail.com 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
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
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 there
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
On Thu, Jun 11, 2009 at 9:59 AM, Davidwizza...@gmail.com 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
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 dep...@depesz.com:
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
On Thu, Jun 11, 2009 at 12:59 PM, Brett Hendersonbr...@bretth.com 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
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 Amoszerebub...@gmail.com 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
On Thu, Jun 11, 2009 at 12:59 PM, Brett Hendersonbr...@bretth.com 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
On Thu, Jun 11, 2009 at 1:13 PM, Brett Hendersonbr...@bretth.com 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
Vyacheslav Kalinin v...@mgcp.com 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.
You
Vyacheslav Kalinin v...@mgcp.com 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
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
On Thu, Jun 11, 2009 at 4:36 PM, Dimitri Fontaine dfonta...@hi-media.comwrote:
Vyacheslav Kalinin v...@mgcp.com 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
On 6/11/09, Brett Henderson br...@bretth.com 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
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 server,
On 6/11/09, Matt Amos zerebub...@gmail.com wrote:
On Thu, Jun 11, 2009 at 1:13 PM, Brett Hendersonbr...@bretth.com 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
On Wed, Jun 10, 2009 at 12:49 PM, Gus
Gutoskishared.entanglem...@gmail.com 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
I need the include files as well so I can build against the library.
Phil
From: Andy Colson a...@squeakycode.net
To: Phil Longstaff plongst...@rogers.com
Cc: pgsql-general@postgresql.org
Sent: Thursday, June 11, 2009 9:47:52 AM
Subject: Re: [GENERAL] Libpq on
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, just the
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
Gutoskishared.entanglem...@gmail.com 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
On Mon, Jun 08, 2009 at 08:08:02PM +0100, Oliver Kohll - Mailing Lists wrote:
On 8 Jun 2009, at 17:23, Merlin Moncure mmonc...@gmail.com 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 didn't
On Thu, Jun 11, 2009 at 4:44 AM, Greg Starkst...@enterprisedb.com 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
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_date
Steven Lembark lemb...@wrkhors.com 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
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
On Thu, Jun 11, 2009 at 2:48 PM, Marko Kreenmark...@gmail.com wrote:
On 6/11/09, Matt Amos zerebub...@gmail.com wrote:
On Thu, Jun 11, 2009 at 1:13 PM, Brett Hendersonbr...@bretth.com wrote:
See pgq.batch_event_sql() function in Skytools [2] for how to
query txids between snapshots
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 not
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:04
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.
However ...
On 6/11/09, Matt Amos zerebub...@gmail.com wrote:
On Thu, Jun 11, 2009 at 2:48 PM, Marko Kreenmark...@gmail.com wrote:
On 6/11/09, Matt Amos zerebub...@gmail.com wrote:
On Thu, Jun 11, 2009 at 1:13 PM, Brett Hendersonbr...@bretth.com wrote:
See pgq.batch_event_sql() function in
On Thu, Jun 11, 2009 at 7:13 PM, Josh Berkusj...@agliodbs.com 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.
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
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 possible? I can run
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.
Unfortunately,
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 ... not sure my
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
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
Brandon Metcalf bran...@geronimoalloys.com 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
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 byrn...@harte-lyne.ca 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() ...
t == t...@sss.pgh.pa.us writes:
t Brandon Metcalf bran...@geronimoalloys.com 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
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
Galway
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 Lanet...@sss.pgh.pa.us wrote:
James B. Byrne byrn...@harte-lyne.ca 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
Brandon Metcalf bran...@geronimoalloys.com 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
t == t...@sss.pgh.pa.us writes:
t Brandon Metcalf bran...@geronimoalloys.com 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
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 Spottsrfu...@gmail.com 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
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 to the
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 =
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
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
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
74 matches
Mail list logo
