Re: [GENERAL] Updating pg_attribute to widen column

Hi Greg, First off, thanks for your reply. I had actually just read your blog before writing this. I should have been more clear in my first post. If you use ALTER TABLE it will check every row in the table to make sure the column doesn't exceed the constraint (in 8.4 - I know this has been upd

Re: [GENERAL] Updating pg_attribute to widen column

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Justin Julicher asked: > We need to widen a column on a table with millions of rows and the only way > to do this currently is to migrate the data from one column to another with > a script and trigger. Not the only way - the canonical way is to

[GENERAL] Updating pg_attribute to widen column

Psql Version: 8.4 Hi, We need to widen a column on a table with millions of rows and the only way to do this currently is to migrate the data from one column to another with a script and trigger. I know how to do this via an update to pg_attribute which would incur the table scan penalty but

Re: [GENERAL] youtube video on pgsql integrity

Tom, thanks for the reply On Thursday, November 29, 2012 at 12:58, Tom Lane wrote: Gavan Schneider writes: pendari=> select now()/0; Bus error: 10 [ scratches head... ] I get the expected error report on my own OS X 10.7.5 machine (though it's just plain Lion not Server). As of Lion the Se

Re: [GENERAL] pg_listening_channels()

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Igor Neyman wrote: > But, I (and probably many others) use LISTEN/NOTIFY mechanism to notify > client program connected to postgres database about changes made in the > database Yes, it is commonly used for that. > Implementation prior to PG

Re: [GENERAL] Question about PostgreSQL logging configuration

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > So at end I getting logs which are incompatible with pgFouine (and > with my custom log analyzer as well). Ah, well, that's hardly Postgres' fault is it? :) You might want to look into other log software - there are at least two others I kn

Re: [GENERAL] Question about PostgreSQL logging configuration

>> I want log all 'mod' statements with their execution times and all >> statements longer than 10ms (also with their execution times). > > You cannot combine things as you want. However, it seems a fairly > minor loss - why would you care about how fast sub-10ms mods > ran? Trouble if I try that

Re: [GENERAL] Question about PostgreSQL logging configuration

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I want log all 'mod' statements with their execution times and all > statements longer than 10ms (also with their execution times). You cannot combine things as you want. However, it seems a fairly minor loss - why would you care about how f

Re: [GENERAL] youtube video on pgsql integrity

Gavan Schneider writes: > pendari=> select now()/0; > Bus error: 10 [ scratches head... ] I get the expected error report on my own OS X 10.7.5 machine (though it's just plain Lion not Server). This looks like it's a psql-side problem, not a server-side problem, particularly since you

Re: [GENERAL] youtube video on pgsql integrity

On Friday, November 30, 2012 at 02:30, czisg0s...@sneakemail.com (Chris Angelico rosuav-at-gmail.com |pg-gts/Basic|) wrote: I like his quoting of the error messages. MySQL: now()/0 -> NULL; PostgreSQL: now()/0 -> "dude, what are you doing". H... that looked amusing in a perverse way, and a

Re: [GENERAL] youtube video on pgsql integrity

On Thu, Nov 29, 2012 at 9:41 AM, Shaun Thomas wrote: > On 11/29/2012 07:48 AM, Ray Stell wrote: > > > http://www.youtube.com/watch?**v=1PoFIohBSM4 >> > > To be fair to MySQL, you can turn all that "helpfulness" off if you set > the SQL mode: > > https:

Re: [GENERAL] Enforcing minimum on many-to-many relationship?

On Thu, Nov 29, 2012 at 1:03 PM, Mike Blackwell wrote: > I want to be able to enforce the condition that there will always be at > least one row in table 'x' for each row in 'a'. I.e, a row in 'a' must > always be related to at least one 'r'. > > My first thought was a trigger at delete time. Th

Re: [GENERAL] About aggregates...

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michael Giannakopoulos Sent: Thursday, November 29, 2012 4:07 PM To: pgsql-general@postgresql.org Subject: [GENERAL] About aggregates... Hello guys, I would like to ask if there is any way to make an

Re: [GENERAL] About aggregates...

Hi, On 30 November 2012 08:06, Michael Giannakopoulos wrote: > However an aggregate function > feeds me one a tuple for each call, but I would like to have access to a > batch of tuples per function call. Is there any possible way to perform > something like this? Yes, this might be good for you

[GENERAL] About aggregates...

Hello guys, I would like to ask if there is any way to make an aggregate function to take a set of tuples as an input variable. I know that an actual aggregate function receives each tuple one at a time and process it on the fly. However I want to store tuples in an incremental fashion so as to pr

[GENERAL] Enforcing minimum on many-to-many relationship?

I have a pair of tables, and a third describing a many-to-many relationship between them. Along the lines of: CREATE TABLE a (a_id integer NOT NULL PRIMARY KEY); CREATE TABLE b (b_id integer NOT NULL PRIMARY KEY); CREATE TABLE x (a_id integer NOT NULL REFERENCES a(a_id) ON DELETE CASCADE, b_id in

Re: [GENERAL] pg_listening_channels()

Igor Neyman writes: >> [ shrug... ] It was debated extensively and the advantages of the new >> implementation were deemed to outweigh the disadvantages. > Are you saying that these two features: attached payload and being able to > find which channels are being listened to - are incompatible?

[GENERAL] Storing files in the database - and giving easy access to this?

Hi Tomas, hi all, Nice blog, Thomas, about "storing files in the database" [1]. Now, I'd like to implement a single client script for using this "remote file system". The solution should implement (1) List (dir), (2) Copy (cp) and (3) Remove (rm). It should be available for both Linux and Window

Re: [GENERAL] When does CLUSTER occur?

Igor Neyman wrote: > As for MS SQL, here is how it implements clustering: > The index on which the table is clustered actually becomes a > "physical holder" (for the lack of better term) of the table, > meaning the complete table tuples are stored in the leaf blocks > of the index it is clustered

Re: [GENERAL] When does CLUSTER occur?

The data files contain only one active version of every record. Different versions of that same record are maintained in a version store. Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Microsoft! They need to add TRUNCATE PARTITION in SQL Server https:/

Re: [GENERAL] When does CLUSTER occur?

From: Mike Christensen [mailto:m...@kitchenpc.com] Sent: Thursday, November 29, 2012 1:35 PM To: stho...@optionshouse.com Cc: Jeff Janes; Schnabel, Robert D.; pgsql-general@postgresql.org Subject: Re: When does CLUSTER occur? On Thu, Nov 29, 2012 at 10:28 AM, Shaun Thomas wrote: On 11/29/2012

Re: [GENERAL] When does CLUSTER occur?

MSSQL uses an index to maintain the cluster. If a record fits between it places it there, if it doesn't, it considers either moving data or adding it physically out of order (while maintaining the index) Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince M

Re: [GENERAL] set value var via execute

From: Peter Kroon [mailto:plakr...@gmail.com] Sent: Thursday, November 29, 2012 11:01 AM To: pgsql-general@postgresql.org Subject: set value var via execute Is it possible to set the value of a var via execute? drop table if exists __test; create unlogged table __test( id int ); DO $$

Re: [GENERAL] youtube video on pgsql integrity

On 30/11/12 04:30, Chris Angelico wrote: On Fri, Nov 30, 2012 at 2:00 AM, Ray Stell wrote: On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: is everything shown there really the behavior of the MySQL database itself? Good question. I intend to install mysql one day to explore, but just can'

Re: [GENERAL] pg_listening_channels()

> -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Wednesday, November 28, 2012 6:13 PM > To: Igor Neyman > Cc: Greg Sabino Mullane; pgsql-general@postgresql.org > Subject: Re: [GENERAL] pg_listening_channels() > > Igor Neyman writes: > > With PG 9.0 changes I lost

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 10:28 AM, Shaun Thomas wrote: > On 11/29/2012 12:20 PM, Jeff Janes wrote: > > It would maintain an imperfect clustering, but still much better than >> current behavior. >> > > I thought about that, too. The "imperfect clustering" made me erase > everything I'd written. If

Re: [GENERAL] When does CLUSTER occur?

On 11/29/2012 12:20 PM, Jeff Janes wrote: It would maintain an imperfect clustering, but still much better than current behavior. I thought about that, too. The "imperfect clustering" made me erase everything I'd written. If the clustering is imperfect, it's not really clustering. It would m

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 9:33 AM, Shaun Thomas wrote: > On 11/29/2012 11:28 AM, Mike Christensen wrote: > >> It's always kinda annoyed me that the CLUSTER command in Postgres >> doesn't work like it does on Microsoft SQL. > > > It's a natural side-effect of MVCC, unfortunately. Data goes wherever i

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 9:33 AM, Shaun Thomas wrote: > On 11/29/2012 11:28 AM, Mike Christensen wrote: > > It's always kinda annoyed me that the CLUSTER command in Postgres >> doesn't work like it does on Microsoft SQL. >> > > It's a natural side-effect of MVCC, unfortunately. Data goes wherever

Re: [GENERAL] When does CLUSTER occur?

-Original Message- From: Jeff Janes [mailto:jeff.ja...@gmail.com] Sent: Thursday, November 29, 2012 11:19 AM To: Schnabel, Robert D. Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] When does CLUSTER occur? On Thu, Nov 29, 2012 at 8:11 AM, Schnabel, Robert D. wrote: > My questi

Re: [GENERAL] youtube video on pgsql integrity

On 11/29/2012 07:48 AM, Ray Stell wrote: http://www.youtube.com/watch?v=1PoFIohBSM4 To be fair to MySQL, you can turn all that "helpfulness" off if you set the SQL mode: https://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html Amusingly enough, they actually have a POSTGRESQL mode. ;)

Re: [GENERAL] When does CLUSTER occur?

On 11/29/2012 11:28 AM, Mike Christensen wrote: It's always kinda annoyed me that the CLUSTER command in Postgres doesn't work like it does on Microsoft SQL. It's a natural side-effect of MVCC, unfortunately. Data goes wherever it goes according to the free space map, or at the end if there a

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 9:19 AM, Jeff Janes wrote: > On Thu, Nov 29, 2012 at 8:11 AM, Schnabel, Robert D. > wrote: > > > My question is whether or not the “ALTER TABLE gen1011 CLUSTER ON > > xgen1011_si_sn” actually clusters the table at that point or if it just > > tells it to use that index fo

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 8:11 AM, Schnabel, Robert D. wrote: > My question is whether or not the “ALTER TABLE gen1011 CLUSTER ON > xgen1011_si_sn” actually clusters the table at that point or if it just > tells it to use that index for clustering? It just marks it to use that index if/when it is

Re: [GENERAL] set value var via execute

Go it: execute 'select id from '||v_table||' order by random() limit 1' into v_holder; 2012/11/29 Peter Kroon > --begin this > fails-- > v_holder = execute 'select id from '||v_table||' ord

[GENERAL] libpq - PQsendQuery wait for complete result

I'm having problem with libpq's *PQexec* function hanging on intermittent connections. After searching around, the solution seems to be to use the asynchronous functions *PQsendQuery*/*PQgetResult* and implement your own timeout. Now the issue I'm facing is that *PQgetResult* needs to be called m

Re: [GENERAL] youtube video on pgsql integrity

Dne Thu, 29 Nov 2012 16:00:55 +0100 Ray Stell napsal(a): On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: is everything shown there really the behavior of the MySQL database itself? Good question. I intend to install mysql one day to explore, but just can't find the time. The particul

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 04:11:44PM +, Schnabel, Robert D. wrote: > Hi, > > I'm looking for some general info on the behavior of CLUSTER. I add large > amounts of data to an existing table something like this: > > BEGIN; > DROP INDEX IF EXISTS xgen1011_si_sn; > COMMIT; > > BEGIN; > INSERT I

[GENERAL] When does CLUSTER occur?

Hi, I'm looking for some general info on the behavior of CLUSTER. I add large amounts of data to an existing table something like this: BEGIN; DROP INDEX IF EXISTS xgen1011_si_sn; COMMIT; BEGIN; INSERT INTO gen1011 [snip] COMMIT; BEGIN; CREATE INDEX xgen1011_si_sn ON gen1011 USING btree

[GENERAL] set value var via execute

Is it possible to set the value of a var via execute? drop table if exists __test; create unlogged table __test( id int ); DO $$ DECLARE v_holder int; v_table text = 'table'; v_record_0 text[]; v_id int; BEGIN execute ' insert into __test(id) select id from '||v_table||' order by random() l

[GENERAL] UPDATE syntax

Need some help with UPDATE syntax. I am attempting to do something like this: WITH default_facility AS ( SELECT facility_id, inkjetorlabel FROM engagement_facility_defs WHERE engagement_facility_def_id = 8 ) UPDATE engagement_faciliti

Re: [GENERAL] youtube video on pgsql integrity

On Fri, Nov 30, 2012 at 2:00 AM, Ray Stell wrote: > > On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: >> is everything shown there really >> the behavior of the MySQL database itself? > > Good question. I intend to install mysql one day to explore, but just can't > find the time. The particu

Re: [GENERAL] youtube video on pgsql integrity

On Nov 29, 2012, at 9:27 AM, Kevin Grittner wrote: > is everything shown there really > the behavior of the MySQL database itself? Good question. I intend to install mysql one day to explore, but just can't find the time. The particular engine is not disclosed and I've read some are better th

Re: [GENERAL] youtube video on pgsql integrity

Kevin Grittner, 29.11.2012 15:27: Ray Stell wrote: http://www.youtube.com/watch?v=1PoFIohBSM4 I really don't want to get into bashing another product or starting a flame war, but I'm curious -- is everything shown there really the behavior of the MySQL database itself? Yes, it is. It *is*

Re: [GENERAL] youtube video on pgsql integrity

Ray Stell wrote: > http://www.youtube.com/watch?v=1PoFIohBSM4 I really don't want to get into bashing another product or starting a flame war, but I'm curious -- is everything shown there really the behavior of the MySQL database itself? It's not the front-end tool or something? It's hard to beli

[GENERAL] youtube video on pgsql integrity

turn the vol down a bit: http://www.youtube.com/watch?v=1PoFIohBSM4 the answer is d) -- 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] Non-replicated index allowing comparison ("<")?

On Nov 29, 2012 8:59 PM, "Björn Edström" wrote: > > Greetings list. > > I have a Postgresql 9.0.8 cluster with one master and a few slaves, in > a hot standby and streaming replication setup. The setup has a single > database with a single table. The table has a column "updated" of type > "timesta

[GENERAL] Non-replicated index allowing comparison ("<")?

Greetings list. I have a Postgresql 9.0.8 cluster with one master and a few slaves, in a hot standby and streaming replication setup. The setup has a single database with a single table. The table has a column "updated" of type "timestamp". For maintenance reasons I'd like to create a B-tree (or