Re: [GENERAL] Preferred usage for 'copy to' for a subset of data
"Jason L. Buberel" <[EMAIL PROTECTED]> writes: > For recent postgres releases, is there any effective difference > (performance/memory/io) between: > create temp table foo as select * from bar where bar.date > '2007-01-01'; > copy foo to '/tmp/bar.out'; > drop table temp; > and this: > copy ( select * from bar where bar.date > '2007-01-01' ) to '/tmp/bar.out'; Surely the latter will be faster, since it doesn't bother to store all the data in a server temp table. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Preferred usage for 'copy to' for a subset of data
For recent postgres releases, is there any effective difference (performance/memory/io) between: create temp table foo as select * from bar where bar.date > '2007-01-01'; copy foo to '/tmp/bar.out'; drop table temp; and this: copy ( select * from bar where bar.date > '2007-01-01' ) to '/tmp/bar.out'; ...that would lead me to use one method vs. the other on large data sets (1M+ records)? Just wondering, Jason
Re: [GENERAL] FATAL: could not reattach to shared memory (Win32)
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Magnus Hagander wrote: > Shelby Cain wrote: > >> - Original Message From: Magnus Hagander > >> <[EMAIL PROTECTED]> To: Alvaro Herrera > >> <[EMAIL PROTECTED]> Cc: Terry Yapt <[EMAIL PROTECTED]>; > >> pgsql-general@postgresql.org Sent: Thursday, August 23, 2007 > >> 3:43:32 PM Subject: Re: [GENERAL] FATAL: could not reattach to > >> shared memory (Win32) > >> > >> > >> 8.3 will have a new way to deal with shared mem on win32. It's the > >> same underlying tech, but we're no longer trying to squeeze it into > >> an emulation of sysv. With a bit of luck, that'll help :-) > >> > >> //Magnus > >> > > > > Wild guess on my part... could that error be the result of an attempt > > to map shared memory into a process at a fixed location that just > > happens to already be occupied by a dll that Windows had decided to > > relocate? > > Not that wild a guess, really :-) I'd say it's a very good possibility - > but I have no idea why it'd do that, since all backends load the same > DLLs at that stage. > > //Magnus > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.4 error restoring dump because of gin__int_ops
"Carlo Stonebanks" <[EMAIL PROTECTED]> writes: > When restoring a dump, I get the following error: > ERROR: could not make operator class "gin__int_ops" be default for type > pg_catalog.int4[] > DETAIL: Operator class "_int4_ops" already is the default. This is a dup of bug #3048. I see that that still has not been fixed in CVS HEAD, but I'll make sure it happens before we wrap the upcoming set of releases. That won't help a whole lot for dealing with an existing broken dump, though. I'd suggest removing the "DEFAULT" keyword from the dump script's definition of gin__int_ops. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What's the difference between SET STORAGE MAIN and EXTENDED?
On 9/7/2007 11:45 AM, Tom Lane wrote:
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes:
Tom Lane =EDrta:
Zoltan Boszormenyi <[EMAIL PROTECTED]> writes:
At the end of the day, the behaviour is the same, isn't it?
No, there's a difference in terms of the priority for pushing this
column out to toast storage, versus pushing other columns of the row
out to toast.
Thanks very much for clarifying.
I was thinking of a binary data that wouldn't fit
into the maximum inline tuple size. In this case
both MAIN and EXTENDED end up compressed
and out-of-line. I didn't consider having multiple
bytea or text columns filled with small amount of data.
It'd be pretty unwise to mark a column MAIN if it's likely to contain
wide values ("wide" meaning more than 1K or so). As you say, it'll
still get toasted --- but not until after everything else in the row has
been toasted, even quite narrow values that happen to be of toastable
types.
Additionally, EXTENDED means that the toaster tries to get the tuple
down to a 1/4 blocksize. With MAIN, it won't do so.
MAIN storage strategy would be for wide columns that you *always* touch
in *every* select *and* update and where the access pattern is always
resulting in an index scan. Only in that case, you save from having the
value right in the main tuple and don't need to pull it from the toast
table and also don't lose the optimization of reusing external toast
values if they aren't touched on update.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
On 9/13/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > Alvaro Herrera wrote: > > Bruce Momjian wrote: > > > > > > Is this item closed? > > > > No, it isn't. Please add a TODO item about it: > > * Prevent long-lived temp tables from causing frozen-Xid advancement > >starvation > > Sorry, I don't understand this. Can you give me more text? Thanks. > s/long-lived/orphaned/ ? And possibly this means better orphan detection and removal. Andrew
Re: [GENERAL] psql hanging
Trevor Talbot wrote: > Unless psql is turning on keepalive or similar, or the OS is forcing > it on by default, there are no timeouts for idle TCP connections. If > the command was transported to the server successfully and psql was > just waiting for a result, the connection is idle and nothing will > happen if the server end suddenly goes away. This is the most likely explanation - fits the symptoms. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] AutoVacuum Behaviour Question
Alvaro Herrera wrote: > Bruce Momjian wrote: > > > > Is this item closed? > > No, it isn't. Please add a TODO item about it: > * Prevent long-lived temp tables from causing frozen-Xid advancement >starvation Sorry, I don't understand this. Can you give me more text? Thanks. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 8.2.4 error restoring dump because of gin__int_ops
When restoring a dump, I get the following error: ERROR: could not make operator class "gin__int_ops" be default for type pg_catalog.int4[] DETAIL: Operator class "_int4_ops" already is the default. I believe the problem lies with: CREATE OPERATOR CLASS gin__int_ops DEFAULT FOR TYPE integer[] USING gin AS STORAGE integer , OPERATOR 3 &&(integer[],integer[]) , OPERATOR 6 =(anyarray,anyarray) RECHECK , OPERATOR 7 @>(integer[],integer[]) , OPERATOR 8 <@(integer[],integer[]) RECHECK , OPERATOR 13 @(integer[],integer[]) , OPERATOR 14 ~(integer[],integer[]) RECHECK , OPERATOR 20 @@(integer[],query_int) , FUNCTION 1 btint4cmp(integer,integer) , FUNCTION 2 ginarrayextract(anyarray,internal) , FUNCTION 3 ginint4_queryextract(internal,internal,smallint) , FUNCTION 4 ginint4_consistent(internal,smallint,internal); I saw a bug report on a bug with a similar error message I found a bug report using linux/8.2.0. Has this been resolved? Is there aworkaround? Carlo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] processing urls with tsearch2
Any way to install the dictionary without the make? As in is there binary versions of it available? I am running postgresql on windows servers... On 9/13/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: > > On Thu, 13 Sep 2007, Laimonas Simutis wrote: > > > Hey guys, > > > > maybe anyone using tsearch2 could advise on this. With the default > > installation, url, host and some other tokens are processed with the > simple > > dictionary. Thus term like mywebsite.com gets stored as 'mywebsite.com'. > The > > parser correctly assigns token id of type host to the term, but then the > > dictionary the terms gets routed through is simple and what gets stored > is > > mywebsite.com > > > > The questions are: > > > > 1) is there a dictionary available that I could utilize that will remove > > .com, .net, .org, etc? I could write one myself, but after seeing some > > sample dictionary implementations and C code I try to avoid, I got > scared a > > bit. > > Yes, we have dict_regex, which was developed by Sergey Karpov, see details > http://lynx.sao.ru/~karpov/software/postgres_dict_regex.html > It uses pcre library and you need to know perl regexps. > > > > > 2) has anyone else dealt with this maybe in a different way? > > sure, preprocess text using prefered language before passing to > ro_tsvector > > > > > > > Thanks for any suggestions and help, > > > > Laimis > > > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 >
Re: [GENERAL] pg_standby observation
On Sep 13, 2007, at 3:02 PM, Jeff Davis wrote: On Thu, 2007-09-13 at 14:05 -0500, Erik Jones wrote: If you include the -d option pg_standby will emit logging info on stderr so you can tack on something like 2>> logpath/standby.log. What it is lacking, however, is timestamps in the output when it successfully recovers a WAL file. Was there something more ou were looking for? I don't think the timestamps will be a problem, I can always pipe it through something else. I think this will work, but it would be nice to have something that's a little more well-defined and standardized to determine whether some kind of error happens during replay. Right. The problem there is that there really isn't anything standardized about pg_standby, yet. Or, if it is, it hasn't been documented, yet. Perhaps you could ask Simon about the possible outputs on error conditions so that you'll have a definite list to work with? Ultimately, what I'm trying to do is make it so that pgsnmpd can monitor this, and trap if a problem occurs. In order for pgsnmpd to do this in a way that works for a large number of people, it can't make too many assumptions about logging options, etc. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_standby observation
On Thu, 2007-09-13 at 14:05 -0500, Erik Jones wrote: > If you include the -d option pg_standby will emit logging info on > stderr so you can tack on something like 2>> logpath/standby.log. > What it is lacking, however, is timestamps in the output when it > successfully recovers a WAL file. Was there something more ou were > looking for? I don't think the timestamps will be a problem, I can always pipe it through something else. I think this will work, but it would be nice to have something that's a little more well-defined and standardized to determine whether some kind of error happens during replay. Ultimately, what I'm trying to do is make it so that pgsnmpd can monitor this, and trap if a problem occurs. In order for pgsnmpd to do this in a way that works for a large number of people, it can't make too many assumptions about logging options, etc. Regards, Jeff Davis ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_standby observation
On Sep 13, 2007, at 1:38 PM, Jeff Davis wrote: I think it would be useful if pg_standby (in version 8.3 contrib) could be observed in some way. Right now I use my own standby script, because every time it runs, it touches a file in a known location. That allows me to monitor that file, and if it is too stale, I know something must have gone wrong (I have an archive_timeout set), and I can send an SNMP trap. Would it be useful to add something similar to pg_standby? Is there a better way to detect a problem with a standby system, or a more appropriate place? The postgres logs do report this also, but it requires more care to properly intercept the "restored log file ... from archive" messages. Regards, Jeff Davis If you include the -d option pg_standby will emit logging info on stderr so you can tack on something like 2>> logpath/standby.log. What it is lacking, however, is timestamps in the output when it successfully recovers a WAL file. Was there something more ou were looking for? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] processing urls with tsearch2
On Thu, 13 Sep 2007, Laimonas Simutis wrote: Hey guys, maybe anyone using tsearch2 could advise on this. With the default installation, url, host and some other tokens are processed with the simple dictionary. Thus term like mywebsite.com gets stored as 'mywebsite.com'. The parser correctly assigns token id of type host to the term, but then the dictionary the terms gets routed through is simple and what gets stored is mywebsite.com The questions are: 1) is there a dictionary available that I could utilize that will remove .com, .net, .org, etc? I could write one myself, but after seeing some sample dictionary implementations and C code I try to avoid, I got scared a bit. Yes, we have dict_regex, which was developed by Sergey Karpov, see details http://lynx.sao.ru/~karpov/software/postgres_dict_regex.html It uses pcre library and you need to know perl regexps. 2) has anyone else dealt with this maybe in a different way? sure, preprocess text using prefered language before passing to ro_tsvector Thanks for any suggestions and help, Laimis Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL Glossary?
Hi all, does anybody know where to find a good list of PostgreSQL terms (including both traditional terms and Postgres-specific ones)? At least a simple list w/o descriptions... Google didn't help me yet :-\ -- Best regards, Nikolay ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pg_standby observation
I think it would be useful if pg_standby (in version 8.3 contrib) could be observed in some way. Right now I use my own standby script, because every time it runs, it touches a file in a known location. That allows me to monitor that file, and if it is too stale, I know something must have gone wrong (I have an archive_timeout set), and I can send an SNMP trap. Would it be useful to add something similar to pg_standby? Is there a better way to detect a problem with a standby system, or a more appropriate place? The postgres logs do report this also, but it requires more care to properly intercept the "restored log file ... from archive" messages. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] processing urls with tsearch2
Hey guys, maybe anyone using tsearch2 could advise on this. With the default installation, url, host and some other tokens are processed with the simple dictionary. Thus term like mywebsite.com gets stored as 'mywebsite.com'. The parser correctly assigns token id of type host to the term, but then the dictionary the terms gets routed through is simple and what gets stored is mywebsite.com The questions are: 1) is there a dictionary available that I could utilize that will remove .com, .net, .org, etc? I could write one myself, but after seeing some sample dictionary implementations and C code I try to avoid, I got scared a bit. 2) has anyone else dealt with this maybe in a different way? Thanks for any suggestions and help, Laimis
Re: [GENERAL] Cannot declare record members NOT NULL
Cultural Sublimation wrote: >> Unfortunately for you, they are not different types. If the OCaml >> binding thinks they are, it's the binding's problem; especially since >> the binding seems to be using a completely lame method of trying to tell >> the difference. > > Hi, > > In OCaml and in other languages with strong type systems, "int4 never NULL" > and "int4 possibly NULL" are definitely different types. I think the source > of the problem here is that SQL has a different philosophy, one where type > constraints are not seen as creating new types. There's no such a thing as a 'type constraint' in SQL, and there's no point in defining a new type. Constraints are on table rows, sometimes not even on the values of columns per se, but on combinations of values... Think something like (table.col1 > table.col2)... is that 'creating a new type'? How'd you define this new type, even in OCaml, assuming that originally both are int4? Is '4' a valid value for that type? Now, some _table_ constraints may be similar to _type_ constraints, but that's a corner case, in SQL. It's much more than "a different philosophy", we're speaking of apples and oranges here. Why should SQL recognize a very limited kind of constraints, and treat them specially by defining a new type? > But anyway if you think that checking pg_attribute is a lame method of > obtaining type information, what do you suggest should be done instead? > What would you do if it were you creating the bindings? I think the bindings get it right, the type *is* "int4 possibly NULL", because that't what the integer type in SQL means. The problem here is that not every language type maps perfectly on a database type (and of course the converse it true). "int4 never NULL" may be stored into a table with appropriate constraints, but still some code is needed at application level to convert it back, because there's no such a native type in PG. Think of dates and times, I believe no language bindings handle them in a totally consistent way with PG types (unless they define special-purpose types with the exact same semantics, which is hardly worth it). So, the application is wrong in expecting a SQL database to return values of type "int4 never NULL". Just write a small conversion layer, changing "int4 possibly NULL" into "int4 never NULL", after reading the data. .TM. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Alternative to drop index, load data, recreate index?
Depesz, Thank you for the suggestion- I thought I had read up on that tool earlier but had somehow managed to forget about it when starting this phase of my investigation. Needless to say, I can confirm the claims made on the project homepage when using very large data sets. - Loading 1.2M records into an indexed table: - pg_bulkload: 5m 29s - copy to: 53m 20s These results were obtained using pg-8.2.4 with pg_bulkload-2.2.0. -jason hubert depesz lubaczewski wrote: On Mon, Sep 10, 2007 at 05:06:35PM -0700, Jason L. Buberel wrote: I am considering moving to date-based partitioned tables (each table = one month-year of data, for example). Before I go that far - is there any other tricks I can or should be using to speed up my bulk data loading? did you try pgbulkload? (http://pgbulkload.projects.postgresql.org/) depesz
Re: [GENERAL] query help
can u refer to row?? howto select * from table where row(#2) like 'J%'?? i wanted to test column storing but not ok as no row refer name/id. many thank yous sincerely siva Original Message Subject: Re: [GENERAL] query help From: [EMAIL PROTECTED] Date: Thu, September 13, 2007 11:46 am To: Alexander Staubo <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org many apologees. right link http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php i flip row to column if ok. but howto query?? sincerely siva Original Message Subject: Re: [GENERAL] query help From: "Alexander Staubo" <[EMAIL PROTECTED]> Date: Thu, September 13, 2007 11:38 am To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html Sorry, but the only difference between your table example and your result example was the absence, in the results, of one of the columns. If you want to constrain by some attribute, then you have to tell us that. I recommend that you buy a book on SQL. Lastly, I don't see what this has to do with the "provokative question" thread. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] query help
[EMAIL PROTECTED] wrote:
hello
i add more column not row for new user. i want all "last like 'J%'".
I get the feeling that the result as you've laid it out is not what we
all think it is. For example:
>>table is
>>+---+---+--+---+
>>| id | one | two | three |
>>+---+---+--+---+
>>| first | Jack | Jill | Mary |
>>| last | Ja | Ji | Ma |
>>+---+---+--+---+
I took that to meant that you have columns 'id', 'one', two', three',
and that 'first' & 'last' are field values. However, it now seems that
'first' & 'last' are column names. If so, this makes no sense. I think
what you wanted to give us was:
+---+---+--+
| id | first | last |
+---+---+--+
| one | Jack | Ja |
| two | Jill | Ji |
| three | Mary | Ma |
result:
+---+---+--+
| id | first | last |
+---+---+--+
| one | Jack | Ja |
| two | Jill | Ji |
So, the query you want is, in fact:
SELECT * FROM your_table WHERE last LIKE ('J%');
If that's not working for you, it's perhaps because you have rows for
columns and columns for rows.
http://www.nabble.com/an-other-provokative-question---tf4394285.html
sincerely
siva
What the heck does this have to do with anything?
Please don't top-post.
brian
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] query help
many apologees. right link http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php i flip row to column if ok. but howto query?? sincerely siva Original Message Subject: Re: [GENERAL] query help From: "Alexander Staubo" <[EMAIL PROTECTED]> Date: Thu, September 13, 2007 11:38 am To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html Sorry, but the only difference between your table example and your result example was the absence, in the results, of one of the columns. If you want to constrain by some attribute, then you have to tell us that. I recommend that you buy a book on SQL. Lastly, I don't see what this has to do with the "provokative question" thread. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] query help
[EMAIL PROTECTED] wrote: > hello > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html > sincerely > siva You add a new _column_ for each user?!? That is hideously broken in so many ways. It makes the trivially easy query you are trying to write rather complicated, prevents you from being able to use indexes for either constraints or performance and requires you to change your table definition anytime you add data. And that's just for starters. Fix your table so it has three columns (id, first, last). Then your query is as easy as: select id,first,last from foo where last like 'J%'; Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html Sorry, but the only difference between your table example and your result example was the absence, in the results, of one of the columns. If you want to constrain by some attribute, then you have to tell us that. I recommend that you buy a book on SQL. Lastly, I don't see what this has to do with the "provokative question" thread. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] query help
hello i add more column not row for new user. i want all "last like 'J%'". http://www.nabble.com/an-other-provokative-question---tf4394285.html sincerely siva Original Message Subject: Re: [GENERAL] query help From: "Alexander Staubo" <[EMAIL PROTECTED]> Date: Thu, September 13, 2007 11:17 am To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > hello > > table is > +---+---+--+---+ > | id | one | two | three | > +---+---+--+---+ > | first | Jack | Jill | Mary | > | last | Ja | Ji | Ma | > +---+---+--+---+ > > result is > ++---+---+ > | id | one | two | > ++---+---+ > | first | Jack | Jill | > | last | Ja | Ji | > ++---+---+ > > query is?? Unless I am missing something crucial, this is SQL 101: select id, one, two from foo; Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > query is?? http://www.w3schools.com/sql/default.asp ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > hello > > table is > +---+---+--+---+ > | id | one | two | three | > +---+---+--+---+ > | first | Jack | Jill | Mary | > | last | Ja | Ji | Ma | > +---+---+--+---+ > > result is > ++---+---+ > | id | one | two | > ++---+---+ > | first | Jack | Jill | > | last | Ja | Ji | > ++---+---+ > > query is?? Unless I am missing something crucial, this is SQL 101: select id, one, two from foo; Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Data Model - Linking to PHP Code - Literature
Stefan Schwarzer wrote: Of course you should really have a data model that knows what it wants to sort by and constructs the query appropriately. The table-drawing code can then ask the data-model for heading-names and sort-order details. It's more work up-front, but you only have to do it once and then you can generate new table layouts very easily. Can you recommend some literature for this subject? Tutorials or something online? I know what a data model is. But I have no idea how "the table-drawing code can then ask the data model" Google around for "metadata" and "data driven/led development" Basically your data model needs to be able to describe itself in enough detail that your code can draw it without having to have lots of parameters set. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] query help
hello table is +---+---+--+---+ | id | one | two | three | +---+---+--+---+ | first | Jack | Jill | Mary | | last | Ja | Ji | Ma | +---+---+--+---+ result is ++---+---+ | id | one | two | ++---+---+ | first | Jack | Jill | | last | Ja | Ji | ++---+---+ query is?? sincerely siva ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Data Model - Linking to PHP Code - Literature
Of course you should really have a data model that knows what it wants to sort by and constructs the query appropriately. The table- drawing code can then ask the data-model for heading-names and sort- order details. It's more work up-front, but you only have to do it once and then you can generate new table layouts very easily. Can you recommend some literature for this subject? Tutorials or something online? I know what a data model is. But I have no idea how "the table-drawing code can then ask the data model" Many thanks! Stef ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database/Table Design for Global Country Statistics
Stefan Schwarzer wrote: Just for the completeness, I attach the final working SQL query: SELECT f.year, f.id, c.name, (f.value / p.value) AS per_capita FROM fish_catch AS f JOIN pop_total AS p USING (year, id) INNER JOIN countries AS c ON f.id = c.id ORDER BY year Make sure you fully specify the order: ORDER BY year, f.id, c.name It might work by chance a few times, but then return rows in an unexpected order later. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database/Table Design for Global Country Statistics
Uiuiui and it gets even worse... I want to implement the possibility to calculate on-the-fly the per Capita values for the selected data set. With the "old" table design it would be something like this: SELECT (fish_catch.y_1970 / pop_total.y_1970), (fish_catch.y_1971 / pop_total.y_1971) FROM . Or, if the fish + population data are in different tables: SELECT f.year, f.country, f.fish_catch p.pop_total (f.fish_catch / p.pop_total) AS fish_per_capita FROM fish_figures f JOIN popn_figures p USING (year, country) ORDER BY f.year, f.country; Muchos gracias, Mr. Postgres! I am really touched by your help! Just for the completeness, I attach the final working SQL query: SELECT f.year, f.id, c.name, (f.value / p.value) AS per_capita FROM fish_catch AS f JOIN pop_total AS p USING (year, id) INNER JOIN countries AS c ON f.id = c.id ORDER BY year ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cannot declare record members NOT NULL
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > Firstly, the output of most queries is of a type not represented > anywhere in the catalogs. It's mostly going to be an undeclared record > whose members are listed in pg_type. So using pg_attribute for anything > like this is probably completely wrong. Right --- it's incapable of working for any query column that's not a syntactically-trivial reference to a table column (which is the basis of the OP's original complaint). The fact that PG even attempts to report that much is just to satisfy some rather limited requirements of the JDBC spec. > If I were writing it I would ignore the attisnull flag altogether and > assume that any column can be NULL. If you like you could use the > typisnull column in pg_type, that *is* enforced since that's an actual > constraint on the type. Unfortunately that won't go far either. typisnull could only be true for a domain type, and the SELECT-output code reports the base type not the domain type of any domain column. Another little problem is that not-null-constrained domains don't actually work, if by "work" you mean that a column putatively of such a type can never contain any nulls. The counterexample here is a LEFT JOIN with such a column on the right side. The SQL spec is silent on what to do in such a case, but PG just goes ahead and performs the left join. I'm of the opinion that not-null-constrained domains were simply a Bad Idea that should never have got into the spec at all. Bottom line is that you should probably never assume that a query result column can't be null. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database/Table Design for Global Country Statistics
Stefan Schwarzer wrote: Umm - not sure what you're after. What's wrong with one of: SELECT ... ORDER BY year, value SELECT ... ORDER BY value, year Or did you want a particular year pulled out of the general list, in which case try something like: SELECT ... ORDER BY (year = 1970), year, value SELECT ... ORDER BY (year <> 1970), year, value This works because booleans are considered sortable too. Uiuiui and it gets even worse... I want to implement the possibility to calculate on-the-fly the per Capita values for the selected data set. With the "old" table design it would be something like this: SELECT (fish_catch.y_1970 / pop_total.y_1970), (fish_catch.y_1971 / pop_total.y_1971) FROM . SELECT year, country, fish_catch, pop_total, (fish_catch / pop_total) AS fish_per_capita FROM my_table ORDER BY year,country Or, if the fish + population data are in different tables: SELECT f.year, f.country, f.fish_catch p.pop_total (f.fish_catch / p.pop_total) AS fish_per_capita FROM fish_figures f JOIN popn_figures p USING (year, country) ORDER BY f.year, f.country; HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database/Table Design for Global Country Statistics
Umm - not sure what you're after. What's wrong with one of: SELECT ... ORDER BY year, value SELECT ... ORDER BY value, year Or did you want a particular year pulled out of the general list, in which case try something like: SELECT ... ORDER BY (year = 1970), year, value SELECT ... ORDER BY (year <> 1970), year, value This works because booleans are considered sortable too. Uiuiui and it gets even worse... I want to implement the possibility to calculate on-the-fly the per Capita values for the selected data set. With the "old" table design it would be something like this: SELECT (fish_catch.y_1970 / pop_total.y_1970), (fish_catch.y_1971 / pop_total.y_1971) FROM . But how would this work with the new design? Sorry for these repeated questions (But it's you guys' fault - you said I had a bad table design :-)) Thanks for any hints! Stef ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Database/Table Design for Global Country Statistics
Stefan Schwarzer wrote:
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_row($cols); }
Thanks for the code. I got it working with a couple of changes. But
then I realized that with the new table design I can't anymore easily
sort by a given year (1970 or 2000). This is surely one of the
advantages of the "old" design, that the use via PHP was quite
straight forward.
Do I have to transfer the query results into a PHP array to sort it
in there, then?
Umm - not sure what you're after. What's wrong with one of:
SELECT ... ORDER BY year, value
SELECT ... ORDER BY value, year
Or did you want a particular year pulled out of the general list, in
which case try something like:
SELECT ... ORDER BY (year = 1970), year, value
SELECT ... ORDER BY (year <> 1970), year, value
This works because booleans are considered sortable too.
Wow, didn't know about the "(year = 1970)" thing. Cool. But
nevertheless, the problem is then with the PHP code above; a different
sorting in the query result, means as well a different coding. Or I have
completely miscoded your draft. But I don't see how it would handle a
resulting array of any order - by year, by name, ascending,
descending... I guess I need to go with the PHP array, no?
Ah, I see - your query-results do need to be ordered the same as the
table, yes.
Of course you should really have a data model that knows what it wants
to sort by and constructs the query appropriately. The table-drawing
code can then ask the data-model for heading-names and sort-order
details. It's more work up-front, but you only have to do it once and
then you can generate new table layouts very easily.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Database/Table Design for Global Country Statistics
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_row($cols); }
Thanks for the code. I got it working with a couple of changes.
But then I realized that with the new table design I can't anymore
easily sort by a given year (1970 or 2000). This is surely one of
the advantages of the "old" design, that the use via PHP was quite
straight forward.
Do I have to transfer the query results into a PHP array to sort
it in there, then?
Umm - not sure what you're after. What's wrong with one of:
SELECT ... ORDER BY year, value
SELECT ... ORDER BY value, year
Or did you want a particular year pulled out of the general list,
in which case try something like:
SELECT ... ORDER BY (year = 1970), year, value
SELECT ... ORDER BY (year <> 1970), year, value
This works because booleans are considered sortable too.
Wow, didn't know about the "(year = 1970)" thing. Cool. But
nevertheless, the problem is then with the PHP code above; a
different sorting in the query result, means as well a different
coding. Or I have completely miscoded your draft. But I don't see how
it would handle a resulting array of any order - by year, by name,
ascending, descending... I guess I need to go with the PHP array, no?
$current_country = -1;
$count = -1;
$cols = array();
while ($row = pg_fetch_array($result))
{
if ($row['name'] != $current_country)
{
if (sizeof($cols) > 0)
{
$count++;
$code7 .= display_table_row($current_country, $cols, $count,
$selectedDataset -> name, $selectedID, $selectedTime);
}
$cols = array();
$current_country = $row['name'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0)
{
$code7 .= display_table_row($current_country, $cols, ($count + 1),
$selectedDataset -> name, $selectedID, $selectedTime);
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database/Table Design for Global Country Statistics
Stefan Schwarzer wrote:
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_row($cols); }
Thanks for the code. I got it working with a couple of changes. But then
I realized that with the new table design I can't anymore easily sort by
a given year (1970 or 2000). This is surely one of the advantages of the
"old" design, that the use via PHP was quite straight forward.
Do I have to transfer the query results into a PHP array to sort it in
there, then?
Umm - not sure what you're after. What's wrong with one of:
SELECT ... ORDER BY year, value
SELECT ... ORDER BY value, year
Or did you want a particular year pulled out of the general list, in
which case try something like:
SELECT ... ORDER BY (year = 1970), year, value
SELECT ... ORDER BY (year <> 1970), year, value
This works because booleans are considered sortable too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Cannot declare record members NOT NULL
On Thu, Sep 13, 2007 at 05:02:10AM -0700, Cultural Sublimation wrote: > In OCaml and in other languages with strong type systems, "int4 never NULL" > and "int4 possibly NULL" are definitely different types. I think the source > of the problem here is that SQL has a different philosophy, one where type > constraints are not seen as creating new types. Well, what SQL thinks has little to do with it (it has DOMAINs for example). The problem is also that postgres doesn't distinguish between the two in normal usage. Just about *every* function can return NULL if given the right circumstances. There is really no way postgres can say something is never null, unless it comes from a column declared as such. > But anyway if you think that checking pg_attribute is a lame method of > obtaining type information, what do you suggest should be done instead? > What would you do if it were you creating the bindings? Firstly, the output of most queries is of a type not represented anywhere in the catalogs. It's mostly going to be an undeclared record whose members are listed in pg_type. So using pg_attribute for anything like this is probably completely wrong. If I were writing it I would ignore the attisnull flag altogether and assume that any column can be NULL. If you like you could use the typisnull column in pg_type, that *is* enforced since that's an actual constraint on the type. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Database/Table Design for Global Country Statistics
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_row($cols); }
Thanks for the code. I got it working with a couple of changes. But
then I realized that with the new table design I can't anymore easily
sort by a given year (1970 or 2000). This is surely one of the
advantages of the "old" design, that the use via PHP was quite
straight forward.
Do I have to transfer the query results into a PHP array to sort it
in there, then?
Thanks for any hints!
Stef
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cannot declare record members NOT NULL
Hi, > The thing behind the RETURNS in a function is always a data type, > regardless if it is one that has been explicitly declared with > CREATE TYPE or implicitly by CREATE TABLE. > > There are no NOT NULL conditions for data types. > > NOT NULL only exists for table columns. Thanks for the informative reply. As I mentioned in the message to Tom, I think I understand the source of the problem. In SQL, type constraints are not seen as creating different types, whereas in OCaml they do. (I still maintain that OCaml's way of doing things is more correct on a fundamental level, though). > Have you considered an ON SELECT ... DO INSTEAD rule? > http://www.postgresql.org/docs/current/static/rules.html > > You could create a table that represents the query and > define a SELECT rule on it. Thanks, I'll investigate that... Regards, C.S. Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cannot declare record members NOT NULL
> Unfortunately for you, they are not different types. If the OCaml > binding thinks they are, it's the binding's problem; especially since > the binding seems to be using a completely lame method of trying to tell > the difference. Hi, In OCaml and in other languages with strong type systems, "int4 never NULL" and "int4 possibly NULL" are definitely different types. I think the source of the problem here is that SQL has a different philosophy, one where type constraints are not seen as creating new types. But anyway if you think that checking pg_attribute is a lame method of obtaining type information, what do you suggest should be done instead? What would you do if it were you creating the bindings? Thanks, C.S. Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
am Thu, dem 13.09.2007, um 13:06:11 +0200 mailte Kai Behncke folgendes: > > why dont you simply alter table disable trigger? > > > > depesz > > > Could you give me an example for that please? > Thank you very much :-), Kai Open psql and type: \h alter table test=*# \h alter table Command: ALTER TABLE Description: change the definition of a table Syntax: ALTER TABLE [ ONLY ] name [ * ] Okay: ALTER TABLE foo DISABLE TRIGGER ALL; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
On Thu, Sep 13, 2007 at 01:06:11PM +0200, Kai Behncke wrote: > Could you give me an example for that please? > Thank you very much :-), Kai i think i gave. ok. again: alter table some_table disable trigger all; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
Hidepesz, Original-Nachricht > Datum: Thu, 13 Sep 2007 12:25:51 +0200 > Von: hubert depesz lubaczewski <[EMAIL PROTECTED]> > An: Kai Behncke <[EMAIL PROTECTED]> > CC: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser?? > On Thu, Sep 13, 2007 at 11:25:39AM +0200, Kai Behncke wrote: > > I want that the user xy (who is no superuser) can Update a systemtable > with: > > UPDATE pg_catalog.pg_class SET reltriggers = 0; > > why dont you simply alter table disable trigger? > > depesz > Could you give me an example for that please? Thank you very much :-), Kai -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
On Thu, Sep 13, 2007 at 11:25:39AM +0200, Kai Behncke wrote: > I want that the user xy (who is no superuser) can Update a systemtable with: > UPDATE pg_catalog.pg_class SET reltriggers = 0; why dont you simply alter table disable trigger? depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
am Thu, dem 13.09.2007, um 11:25:39 +0200 mailte Kai Behncke folgendes: > But always if I sent as user xy the > "UPDATE pg_catalog.pg_class SET reltriggers = 0;"-command I get: > > "SQL error: > > ERROR: permission denied for relation pg_class" > > Why is that? MUST I be a superuser for that? Write a function with SECURITY DEFINER for that. *untested* Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] UPDATE pg_catalog.pg_class as NO Superuser??
Dear list, on my system I have multiple user. I want that the user xy (who is no superuser) can Update a systemtable with: UPDATE pg_catalog.pg_class SET reltriggers = 0; With psql I already wrote: ALTER TABLE pg_catalog.pg_class OWNER TO xy; and GRANT ALL PRIVILEGES ON pg_catalog.pg_class TO xy; I check it with \dS and see: List of relations Schema | Name | Type | Owner +--+---+--- pg_catalog | pg_aggregate | table | postgres pg_catalog | pg_am| table | postgres pg_catalog | pg_amop | table | postgres pg_catalog | pg_amproc| table | postgres pg_catalog | pg_attrdef | table | postgres pg_catalog | pg_attribute | table | postgres pg_catalog | pg_auth_members | table | postgres pg_catalog | pg_authid| table | postgres pg_catalog | pg_autovacuum| table | postgres pg_catalog | pg_cast | table | postgres pg_catalog | pg_class | table | xy But always if I sent as user xy the "UPDATE pg_catalog.pg_class SET reltriggers = 0;"-command I get: "SQL error: ERROR: permission denied for relation pg_class" Why is that? MUST I be a superuser for that? Is there any way to set that command as user xy? Thank you very much in advance, Kai -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] get a list of table modifications in a day?
am Thu, dem 13.09.2007, um 10:44:41 +0200 mailte Ottavio Campana folgendes: > > http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html > > since I already use triggers on that table, can I use table_log? > > I mean, can I have two triggers for the same event on the same table? Yes. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] get a list of table modifications in a day?
hubert depesz lubaczewski ha scritto: > On Thu, Sep 13, 2007 at 09:59:30AM +0200, Ottavio Campana wrote: >> 1) pg_dump each day and run diff > > it will become increasingly painful as the table size increases. > >> 2) modify some triggers we use and store the information in another table > > this is the best choice. you can use table_log extension to avoid > writing your own triggers. > > http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html since I already use triggers on that table, can I use table_log? I mean, can I have two triggers for the same event on the same table? signature.asc Description: OpenPGP digital signature
Re: [GENERAL] get a list of table modifications in a day?
"Ottavio Campana" <[EMAIL PROTECTED]> writes: > I need to generate a diff (or something similar) of a table, day by day. > What is the best way to tack insert/update/delete operations? I have two > ideas, and I'd like to hear your opinion: > > 1) pg_dump each day and run diff You can't use pg_dump directly as the rows are unordered. An update will remove the old row in one place and put the new row possibly in a completely different place. Some operations like CLUSTER or VACUUM FULL could move around rows which doesn't matter to SQL but would show up in a diff. You would have to COPY to a file a query which includes an ORDER BY. > 2) modify some triggers we use and store the information in another table > > I am not aware of any functionality offered by postgresql. Does it exists? Well alternatively you could do the same as 1) but do it in SQL. Something like CREATE TABLE copy_table AS (SELECT * FROM original_table); ... wait a day SELECT * FROM copy_table EXCEPT SELECT * FROM original_table It's not going to be fast though. Probably the triggers are the best option really. They give you more information than a diff in that they tell you when the change occurred, what user made the change, and if multiple changes to the same record occurred you get a record of each. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] importing pgsql schema into visio (for diagramming)
Hi list, Le jeudi 13 septembre 2007, Andrew Hammond a écrit : > Does anyone know where I could find a tool which allows importing schema > information from a postgres database into visio? The boss guys want some > pretty pictures... Druid[1] is somewhat capable of delivering nice pictures out of an existing database, though not on an automated way --- you have to import each table one by one then sketch the schema out of this yourself, only linking of tables is automatic. But the advantage not found on other tools is its ability to create as much E/R views as needed, with whatever tables set on them (same table on several E/R views is possible). This allows for hand-crafting nice part picture to print, each one corresponding to a logical part of the database instead of what was easy to print out on some A4/letter pages. For fully automatic processing, you can also try SchemaSpy[2] which is ok for online browsing the schema, but not so much for dead-tree form. [1] http://druid.sourceforge.net/ [2] http://schemaspy.sourceforge.net/ Hope this helps, regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] get a list of table modifications in a day?
Hi PgQ can be used this purpose. Idea is to have triggers on table that push events into queue and then on that queue you can do whatever suits you best. As we don't want to keep these logs online PgQ is most conenient as it efficiently removes them as soon as they are handled. PgQ - table_dispatcher.py Has url encoded events as data source and writes them into table on target database. Used to partiton data. For example change log's that need to kept online only shortly can be written to daily tables and then dropped as they become irrelevant. Also allows to select which columns have to be written into target database Creates target tables according to configuration file as needed PgQ - cube_dispatcher.py Has url encoded events as data source and writes them into partitoned tables in target database. Logutriga is used to create events. Used to provide batches of data for business intelligence and data cubes. Only one instance of each record is stored. For example if record is created and then updated twice only latest version of record stays in that days table. Does not support deletes (not that it is hard to support just we have no need for it). PgQ - queue_archiver.py Writes queue contents into file. Used for backing up queue contents for safety. regards, Asko On 9/13/07, Ottavio Campana <[EMAIL PROTECTED]> wrote: > > I need to generate a diff (or something similar) of a table, day by day. > What is the best way to tack insert/update/delete operations? I have two > ideas, and I'd like to hear your opinion: > > 1) pg_dump each day and run diff > 2) modify some triggers we use and store the information in another table > > I am not aware of any functionality offered by postgresql. Does it exists? > > If not, which solution would you prefer? > > >
Re: [GENERAL] Cannot declare record members NOT NULL
Cultural Sublimation wrote: [has a problem because a SETOF RECORD function can return NULLs in record attributes] >The client software obtains the type > information by querying Postgresql, namely by checking the attnotnull > column in the pg_attribute catalog. Therefore, this is not an > inference error on the client side, but instead a case of Postgresql > providing wrong information. > > Well, we could argue all day on whether this is a bug or a feature, > but the fact is that it is a huge setback for me. I wanted my clients > to access the database indirectly, via a function such as get_movies, > but this problem makes that impossible. The thing behind the RETURNS in a function is always a data type, regardless if it is one that has been explicitly declared with CREATE TYPE or implicitly by CREATE TABLE. There are no NOT NULL conditions for data types. NOT NULL only exists for table columns. So if your function returns "movies", this is the data type "movies" and not the table "movies". The data type does not have constraints. If you check attnotnull of pg_attribute, that is a column of the table "movies", not the data type. Hence your confusion, which is quite understandable, because type and table have the same name. > So, barring functions and views, is there any other way to encapsulate > the inner workings of a query away from clients? Have you considered an ON SELECT ... DO INSTEAD rule? http://www.postgresql.org/docs/current/static/rules.html You could create a table that represents the query and define a SELECT rule on it. Yours, Laurenz Albe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] get a list of table modifications in a day?
On Thu, Sep 13, 2007 at 09:59:30AM +0200, Ottavio Campana wrote: > 1) pg_dump each day and run diff it will become increasingly painful as the table size increases. > 2) modify some triggers we use and store the information in another table this is the best choice. you can use table_log extension to avoid writing your own triggers. http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ON UPDATE trigger question
On Wed, Sep 12, 2007 at 01:56:13PM -0500, Josh Trutwin wrote: > Or would you have to compare each field in OLD, NEW to see if > anything actually changed? you dont have to compare all columns (at least not in 8.2 and newer). please take a look at http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/ make sure you'll also read comment from david fetter. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] get a list of table modifications in a day?
I need to generate a diff (or something similar) of a table, day by day. What is the best way to tack insert/update/delete operations? I have two ideas, and I'd like to hear your opinion: 1) pg_dump each day and run diff 2) modify some triggers we use and store the information in another table I am not aware of any functionality offered by postgresql. Does it exists? If not, which solution would you prefer? signature.asc Description: OpenPGP digital signature
