[GENERAL] Use of search path in plpgsql functions, PG 8.3.12
Hello, At present i have the following Schema 1 - Table: events Public Schema - Table: events Function CREATE OR REPLACE FUNCTION "public"."recurring_events_for" ( "range_start" timestamp, "range_end" timestamp, "time_zone" varchar, "events_limit" integer ) RETURNS SETOF "events" AS If i set the search path to schema1,public the function still returns rows from the events table in the public schema. I would like to use the same function for both schema's. I'm sure i'm missing something obvious, can anyone help me understand this better ? Thanks -- Troy Rasiah -- 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] diagram tools?
On Wed, Nov 24, 2010 at 10:23:15AM -0800, DM wrote: > There are many of them, I use SchemaSpy java based - easy to generate. Here are several other possibilities, which I've taken from the helpful pg_docbot that lives on Freenode in #postgresql. For whatever it's worth, I don't know that I've used any of these except the first link; it has proven itself fairly helpful. http://www.rbt.ca/autodoc/ http://druid.sf.net/ http://www.sqlmanager.net/ http://www.casestudio.com/ http://www.hardgeus.com/projects/pgdesigner/ http://www.thekompany.com/products/dataarchitect/ http://uml.sourceforge.net/index.php http://schemaspy.sourceforge.net/ http://pgfoundry.org/projects/autograph/ http://archives.postgresql.org/pgsql-general/2008-05/msg00918.php http://mogwai.sourceforge.net/?Welcome:ERDesigner_NG http://www.dbvis.com/products/dbvis/ http://www.modelsphere.org/ http://www.sqlpower.ca/page/architect -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] plpyhton
On Wed, Nov 24, 2010 at 11:56:16AM +0530, c k wrote: > Hello, > Does calling a pl/python function from each database connection load the > python interpreter each time? what are the effects of using pl/python > function in a environment where no. of concurrent connections are more and > each user calls a pl/python function? > > Please give the details about how pl/python functions are executed. > Thanks and regards, > > CPK I don't know plpython terribly well, but for most PLs, calling them once in a session loads any interpreter they require. That interpreter remains loaded for the duration of the session. So each individual connection will load its own interpreter, once, at the time of the first function call requiring that interpreter. Most widely used languages also cache various bits of important information about the functions you run, the first time you run them in a session, to avoid needing to look up or calculate that information again when you run the function next time. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [GENERAL] is any of the SQL parser exposed ?
On 25/11/10 13:04, Mark Rostron wrote: Hi We are running a mixture of 8.3 and 8.4 server versions. We are putting together a historical log of statement patterns of long running statement output ( via log_min_duration_statement), extracted from csv files in pg_log. I would like to take a statement of form, say, “select columns from mytable where column1 = “hard-code-text-value’” (or whatever) And then store it as a more general pattern like “select columns from mytable where column1 = $1” The latter form would be more useful for profiling an app. Check out pgfouine - http://pgfouine.projects.postgresql.org/ - it already does all of that sort of thing for you. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is any of the SQL parser exposed ?
Hi We are running a mixture of 8.3 and 8.4 server versions. We are putting together a historical log of statement patterns of long running statement output ( via log_min_duration_statement), extracted from csv files in pg_log. I would like to take a statement of form, say, "select columns from mytable where column1 = "hard-code-text-value'" (or whatever) And then store it as a more general pattern like "select columns from mytable where column1 = $1" The latter form would be more useful for profiling an app. However, I cannot find a simple way to get the database to expose the parse results at any point. Any thoughts? Mr
[GENERAL] PG 9.0.1 StackBuilder wants to install pgJDBC v8.4
I've been running PG 9.0 beta 4 on my local workstation, and decided to finally upgrade tonight to the released version 9.0.1-1 on Windows. I used the one-click installer; many thanks to EnterpriseDB for providing these. Installation completed without issue, then offered to run StackBuilder. I ran it, thinking I would use it to download the latest JDBC driver. I was surprised to see it offer pgJDBC v8.4-701-2. The pgJDBC project site has version 9.0-801 available. Why is StackBuilder listing an old 8.4 JDBC version during a 9.0 install when a 9.0 version is available? Doesn't StackBuilder get the list of available files dynamically off the web? Thanks. -- Guy Rouillier -- 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] alter table add column - specify where the column will go?
On Nov 24, 2010, at 9:42 AM, Derrick Rice wrote: > > Even if an example doesn't exist, you can at least imagine a scenario where > an improvement to postgresql is made such that the column order is decided > internally, rather than by table definition. Not when SQL compatibility requires that the order be maintained. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Postgres 9.01 and WAL files issue
Hi Mathew,
Thanks for your reply.
Its a test machine, I am testing streaming replication with archive.
I figured out he problem, the /mnt/nfs didnt had enough space.
Thanks for looking into this issue.
Thanks
Deepak
On Wed, Nov 24, 2010 at 10:36 AM, Matthew Walden wrote:
> Deepak,
>
> Does your backup script exclude the pg_xlog directory? There is no point
> backing up the WAL files but you will want to make sure the archived WAL
> files are added to the backup set afterwards.
>
> How many files are in pg_xlog at the point where you stop the backup? It
> may actually be that it takes a while to copy them all to the NFS if your
> database has been active overnight. I would hope that it is a low traffic
> system if you are leaving it in backup mode all night though.
>
> Ideally you would build the stop and start SQL into a backup script which
> handles the rsync and then also configure the script to add the archived WAL
> files to the backup set afterwards.
>
> Another thing I would try is to run your exact archive command manually
> (using one of the completed WAL files as an example) to the NFS. See what
> feedback you get as to why it isn't returning either a positive or negative
> return.
>
>
> On Wed, Nov 24, 2010 at 5:52 PM, DM wrote:
>
>> Hi All,
>>
>> pg_stop_backup is not stopping...
>>
>> Postgresql version 9.01
>> OS: Centos
>> Postgresql.conf ==> default configuration did not change anything
>>
>> Steps
>>
>> 1. Initiated psql -c "SELECT pg_start_backup('label', true)"
>> 2. Started RSYNC job to sync slave box
>> rsync -a /var/lib/pgsql/data/ dbpg9-test-02:/var/lib/pgsql/data
>>
>> Left over night to rsync - it was only around 10 GB of data to be rsynced,
>> but i left overnight...
>>
>> 3. psql -c "SELECT pg_stop_backup()"
>>
>>
>> *Gettign Error Message as below*
>>
>> NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to
>> be archived
>> WARNING: pg_stop_backup still waiting for all required WAL segments to be
>> archived (60 seconds elapsed)
>> HINT: Check that your archive_command is executing properly.
>> pg_stop_backup can be cancelled safely, but the database backup will not be
>> usable without all the WAL segments.
>> WARNING: pg_stop_backup still waiting for all required WAL segments to be
>> archived (120 seconds elapsed)
>> HINT: Check that your archive_command is executing properly.
>> pg_stop_backup can be cancelled safely, but the database backup will not be
>> usable without all the WAL segments.
>>
>> WARNING: pg_stop_backup still waiting for all required WAL segments to be
>> archived (240 seconds elapsed)
>> HINT: Check that your archive_command is executing properly.
>> pg_stop_backup can be cancelled safely, but the database backup will not be
>> usable without all the WAL segments.
>>
>> I waited quite long and it was not stopping, I had to issue Ctrl+c to
>> cancel it.
>>
>>
>>
>>
>> I tried to start backup and stop backup again, same problem.
>>
>> Here is my Archive Command:
>> archive_command = 'cp -i %p /mnt/nfs/primary/%f < /dev/null'
>>
>> Directory /mnt/nfs/primary ==> is an NFS mount.
>>
>>
>> Any solution how to fix it.
>>
>>
>> Thanks
>> Deepak Murthy
>>
>>
>>
>>
>>
Re: [GENERAL] Postgres 9.01 and WAL files issue
Deepak,
Does your backup script exclude the pg_xlog directory? There is no point
backing up the WAL files but you will want to make sure the archived WAL
files are added to the backup set afterwards.
How many files are in pg_xlog at the point where you stop the backup? It
may actually be that it takes a while to copy them all to the NFS if your
database has been active overnight. I would hope that it is a low traffic
system if you are leaving it in backup mode all night though.
Ideally you would build the stop and start SQL into a backup script which
handles the rsync and then also configure the script to add the archived WAL
files to the backup set afterwards.
Another thing I would try is to run your exact archive command manually
(using one of the completed WAL files as an example) to the NFS. See what
feedback you get as to why it isn't returning either a positive or negative
return.
On Wed, Nov 24, 2010 at 5:52 PM, DM wrote:
> Hi All,
>
> pg_stop_backup is not stopping...
>
> Postgresql version 9.01
> OS: Centos
> Postgresql.conf ==> default configuration did not change anything
>
> Steps
>
> 1. Initiated psql -c "SELECT pg_start_backup('label', true)"
> 2. Started RSYNC job to sync slave box
> rsync -a /var/lib/pgsql/data/ dbpg9-test-02:/var/lib/pgsql/data
>
> Left over night to rsync - it was only around 10 GB of data to be rsynced,
> but i left overnight...
>
> 3. psql -c "SELECT pg_stop_backup()"
>
>
> *Gettign Error Message as below*
>
> NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to
> be archived
> WARNING: pg_stop_backup still waiting for all required WAL segments to be
> archived (60 seconds elapsed)
> HINT: Check that your archive_command is executing properly.
> pg_stop_backup can be cancelled safely, but the database backup will not be
> usable without all the WAL segments.
> WARNING: pg_stop_backup still waiting for all required WAL segments to be
> archived (120 seconds elapsed)
> HINT: Check that your archive_command is executing properly.
> pg_stop_backup can be cancelled safely, but the database backup will not be
> usable without all the WAL segments.
>
> WARNING: pg_stop_backup still waiting for all required WAL segments to be
> archived (240 seconds elapsed)
> HINT: Check that your archive_command is executing properly.
> pg_stop_backup can be cancelled safely, but the database backup will not be
> usable without all the WAL segments.
>
> I waited quite long and it was not stopping, I had to issue Ctrl+c to
> cancel it.
>
>
>
>
> I tried to start backup and stop backup again, same problem.
>
> Here is my Archive Command:
> archive_command = 'cp -i %p /mnt/nfs/primary/%f < /dev/null'
>
> Directory /mnt/nfs/primary ==> is an NFS mount.
>
>
> Any solution how to fix it.
>
>
> Thanks
> Deepak Murthy
>
>
>
>
>
Re: [GENERAL] diagram tools?
There are many of them, I use SchemaSpy java based - easy to generate. http://schemaspy.sourceforge.net/ Thanks Deepak On Wed, Nov 24, 2010 at 8:08 AM, Dan Armbrust < daniel.armbrust.l...@gmail.com> wrote: > Can anyone recommend a PostgreSQL compatible free tool that I can use > to generate some schema diagrams of an existing database? > > Thanks > > Dan > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Postgres 9.01 and WAL files issue
Hi All,
pg_stop_backup is not stopping...
Postgresql version 9.01
OS: Centos
Postgresql.conf ==> default configuration did not change anything
Steps
1. Initiated psql -c "SELECT pg_start_backup('label', true)"
2. Started RSYNC job to sync slave box
rsync -a /var/lib/pgsql/data/ dbpg9-test-02:/var/lib/pgsql/data
Left over night to rsync - it was only around 10 GB of data to be rsynced,
but i left overnight...
3. psql -c "SELECT pg_stop_backup()"
*Gettign Error Message as below*
NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to
be archived
WARNING: pg_stop_backup still waiting for all required WAL segments to be
archived (60 seconds elapsed)
HINT: Check that your archive_command is executing properly.
pg_stop_backup can be cancelled safely, but the database backup will not be
usable without all the WAL segments.
WARNING: pg_stop_backup still waiting for all required WAL segments to be
archived (120 seconds elapsed)
HINT: Check that your archive_command is executing properly.
pg_stop_backup can be cancelled safely, but the database backup will not be
usable without all the WAL segments.
WARNING: pg_stop_backup still waiting for all required WAL segments to be
archived (240 seconds elapsed)
HINT: Check that your archive_command is executing properly.
pg_stop_backup can be cancelled safely, but the database backup will not be
usable without all the WAL segments.
I waited quite long and it was not stopping, I had to issue Ctrl+c to cancel
it.
I tried to start backup and stop backup again, same problem.
Here is my Archive Command:
archive_command = 'cp -i %p /mnt/nfs/primary/%f < /dev/null'
Directory /mnt/nfs/primary ==> is an NFS mount.
Any solution how to fix it.
Thanks
Deepak Murthy
Re: [GENERAL] alter table add column - specify where the column will go?
Stuart McGraw writes: > This is the first time I've ever looked at the 1000+ page spec and I > haven't tried to chase down all the definitions so I don't pretend to > be authoritative but it sure sounds to me (as your observation above > implies) that SQL *does* have an explicit notion of column order. Yes, it does. If it did not, they would never have provided the option of omitting the target-column-name list from INSERT. As for the original issue, the ability to add a column somewhere other than at the end is on the TODO list, but it's been there for quite some time so don't hold your breath waiting for it to get done. There are several discussions in the pgsql-hackers archives about why it isn't a simple thing to do. In the meantime, if the OP wants it bad enough he can do something involving CREATE TABLE ... AS SELECT ... to build a new table with the columns in the desired order, and then rename it to replace the old table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting current and average on a single row
Adrian Klaver wrote: On Wednesday 24 November 2010 1:08:27 am Mark Morgan Lloyd wrote: What is best practice when extracting both current and average from a table? Demonstration table here contains data from a cheap weather station. I can obviously get the current reading like this: select temp_out, dewpoint from weather where datetime between (now() - '10 minutes'::interval) and now() order by datetime desc limit 1; and I can get averages like this: select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now(); In both cases there are a dozen or so columns in total. How are these best merged to yield a single row? Some form of join, or window functions? I am not seeing a dozen columns, maybe rows? I'd only put in a couple of columns as an example, but I was also making the point that it would be nice to avoid having to type in an excessive number of column names. I quick and dirty solution(testing needed): select temp_out,dewpoint,atbl.avg_temp_out,atbl.avg_dewpoint from (select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now()) as atbl, weather order by datetime desc limit 1; Has to be a bit more complex than that to make sure that the current reading really is current: select ctbl.temp_out,ctbl.dewpoint, atbl.avg_temp_out,atbl.avg_dewpoint from ( select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now() ) as atbl, ( select * from weather where datetime between (now() - '8 minutes'::interval) and now() order by datetime desc limit 1 ) as ctbl; explain prices that as Nested Loop (cost=8.30..16.62 rows=1 width=84). I think it's more elegant than the SQL I'm currently using select * from ( select * from weather where datetime between (now() - '10 minutes'::interval) and now() order by datetime desc limit 1 ) as foo left outer join ( select datetime, avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now() group by datetime ) as bar using (datetime); but I note that explain prices that as Nested Loop Left Join (cost=0.02..16.63 rows=1 width=215). Does that mean that the query using the nested join will, on average, be more efficient? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] alter table add column - specify where the column will go?
On 11/24/2010 03:32 AM, Peter Bex wrote: > On Wed, Nov 24, 2010 at 09:37:02AM +, Grzegorz Jaśkiewicz wrote: >> just never use SELECT *, but always call columns by names. You'll >> avoid having to depend on the order of columns, which is never >> guaranteed, even if the table on disk is one order, the return columns >> could be in some other. > > People have been saying that on this list forever, and I agree you > shouldn't *depend* on column order, but why does INSERT syntax allow > you to omit the column names? > > INSERT INTO sometable VALUES (1, 2, 3); > > If columns inherently don't have an ordering, this shouldn't be > possible because it would make no sense. Looking in an old copy of a draft 2003 sql standard, sec-7.12 (p 341) which describes queries, Syntax Rules, para 3 describes the * select list and 3b says, ... The columns are referenced in the ascending sequence of their ordinal position within T. ... This is the first time I've ever looked at the 1000+ page spec and I haven't tried to chase down all the definitions so I don't pretend to be authoritative but it sure sounds to me (as your observation above implies) that SQL *does* have an explicit notion of column order. Perhaps those claiming that no order is guaranteed by SELECT * could provide some support for that from the SQL standards? -- 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] alter table add column - specify where the column will go?
On Wed, Nov 24, 2010 at 4:43 AM, Thomas Kellerer wrote: > Grzegorz Jaśkiewicz, 24.11.2010 10:37: > > just never use SELECT *, but always call columns by names. You'll >> avoid having to depend on the order of columns, which is never >> guaranteed, even if the table on disk is one order, the return columns >> could be in some other. >> >> I always try to convince people of this as well, but when they ask me > under which circumstances this could happen, I can't think of a proper > example. > > Does anybody have an example that would show this? > > Regards > Thomas > Even if an example doesn't exist, you can at least imagine a scenario where an improvement to postgresql is made such that the column order is decided internally, rather than by table definition. If the warning isn't given now, that improvement won't be possible. So I read that as "Don't rely on the table order, it's not part of the interface/contract and we're going to change it if we want to". Derrick
Re: [GENERAL] Postgres 9 and postgis1.5.2
Hi, On Wed, Nov 24, 2010 at 11:34:42AM -0500, akp geek wrote: > One more question. What version of perl do you have? and do we have to > install perl at all or we can use the one that comes with OS? I compiled without perl support; however, the OS-provided perl version should be ok (if you're going for a 32-bit build). - Sebastian -- A: Maybe because some people are too annoyed by top-posting. Q: Why do I not get an answer to my question(s)? A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? -- 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] Postgres 9 and postgis1.5.2
One more question. What version of perl do you have? and do we have to install perl at all or we can use the one that comes with OS? Regards On Wed, Nov 24, 2010 at 11:14 AM, akp geek wrote: > Thanks for sharing the info. I will post my experiences > > Regards > > > On Wed, Nov 24, 2010 at 10:59 AM, Sebastian Jaenicke < > sjaen...@cebitec.uni-bielefeld.de> wrote: > >> On Wed, Nov 24, 2010 at 10:47:18AM -0500, akp geek wrote: >> [..] >> > Did any one of you build Postgres 9 and postgis package >> on >> > solaris 10? I am planning to do one. Any suggestions? >> >> Postgres 9.0 yes, PostGIS no. >> >> Sun Studio compiler 12.2, with CFLAGS="-m64 -xO3 -xarch=native" >> >> src/interfaces/ecpg/preproc/preproc.c # compile by hand without -x03, >> # seems to be a compiler bug >> >> If compiling with dtrace support (--enable-dtrace DTRACEFLAGS="-64"), >> you might be affected by >> >> http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6672627 >> >> >> - Sebastian >> >> -- >> A: Maybe because some people are too annoyed by top-posting. >> Q: Why do I not get an answer to my question(s)? >> A: Because it messes up the order in which people normally read text. >> Q: Why is top-posting such a bad thing? >> >> -- >> 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] Postgres 9 and postgis1.5.2
Thanks for sharing the info. I will post my experiences Regards On Wed, Nov 24, 2010 at 10:59 AM, Sebastian Jaenicke < sjaen...@cebitec.uni-bielefeld.de> wrote: > On Wed, Nov 24, 2010 at 10:47:18AM -0500, akp geek wrote: > [..] > > Did any one of you build Postgres 9 and postgis package > on > > solaris 10? I am planning to do one. Any suggestions? > > Postgres 9.0 yes, PostGIS no. > > Sun Studio compiler 12.2, with CFLAGS="-m64 -xO3 -xarch=native" > > src/interfaces/ecpg/preproc/preproc.c # compile by hand without -x03, > # seems to be a compiler bug > > If compiling with dtrace support (--enable-dtrace DTRACEFLAGS="-64"), > you might be affected by > > http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6672627 > > > - Sebastian > > -- > A: Maybe because some people are too annoyed by top-posting. > Q: Why do I not get an answer to my question(s)? > A: Because it messes up the order in which people normally read text. > Q: Why is top-posting such a bad thing? > > -- > 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] Getting current and average on a single row
On Wednesday 24 November 2010 1:08:27 am Mark Morgan Lloyd wrote: > What is best practice when extracting both current and average from a > table? Demonstration table here contains data from a cheap weather station. > > I can obviously get the current reading like this: > > select temp_out, dewpoint > from weather > where datetime between (now() - '10 minutes'::interval) and now() > order by datetime desc > limit 1; > > and I can get averages like this: > > select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint > from weather > where datetime between (now() - '45 minutes'::interval) and now(); > > In both cases there are a dozen or so columns in total. How are these > best merged to yield a single row? Some form of join, or window functions? I am not seeing a dozen columns, maybe rows? I quick and dirty solution(testing needed): select temp_out,dewpoint,atbl.avg_temp_out,atbl.avg_dewpoint from (select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now()) as atbl, weather order by datetime desc limit 1; > > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or colleagues] -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] diagram tools?
Can anyone recommend a PostgreSQL compatible free tool that I can use to generate some schema diagrams of an existing database? Thanks Dan -- 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] Postgres 9 and postgis1.5.2
On Wed, Nov 24, 2010 at 10:47:18AM -0500, akp geek wrote: [..] > Did any one of you build Postgres 9 and postgis package on > solaris 10? I am planning to do one. Any suggestions? Postgres 9.0 yes, PostGIS no. Sun Studio compiler 12.2, with CFLAGS="-m64 -xO3 -xarch=native" src/interfaces/ecpg/preproc/preproc.c # compile by hand without -x03, # seems to be a compiler bug If compiling with dtrace support (--enable-dtrace DTRACEFLAGS="-64"), you might be affected by http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6672627 - Sebastian -- A: Maybe because some people are too annoyed by top-posting. Q: Why do I not get an answer to my question(s)? A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 9 and postgis1.5.2
Hi All - Did any one of you build Postgres 9 and postgis package on solaris 10? I am planning to do one. Any suggestions? Regards
Re: [GENERAL] alter table add column - specify where the column will go?
Fredric Fredricson wrote: > But if you change the column names in the second SELECT in the UNION this is > ignored: > # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4) > AS x; > c1 | c2 > + > 1 | 2 > 2 | 1 > Apparently, in a UNION the column names are derived from the first statement > only. The example upthread demonstrates that in certain contexts, column positions are relevant whereas column names are not. The modified query you show here doesn't lead to any different conclusion. The allegation that row.* doesn't come with a deterministic column order remains pretty much unsubstantiated at this point. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] alter table add column - specify where the column will go?
On 11/24/2010 12:31 PM, Florian Weimer wrote: * Grzegorz Jaśkiewicz: 2010/11/24 Florian Weimer: * Grzegorz Jaśkiewicz: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. This can't be true because several SQL features rely on deterministic column order. Here's an example: SELECT 1 AS a, 2 AS b UNION SELECT 3 AS b, 4 AS a; a | b ---+--- 1 | 2 3 | 4 (2 rows) Yes, most DBs do a good job to keep it consistent, but they don't have to. So unless you specify column names explicitly (like you did in the example above), there's no guarantees. If the database looked at the column names, the result would be (1, 2), (4, 3), not (1, 2), (3, 4). It seems that UNION does not do what you think it does. Consider you original example, slightly altered: # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c2, 1 AS c1) AS x; c1 | c2 + 1 | 2 2 | 1 If you change a column name in the first SELECT in the UNION: # SELECT c1,c2 FROM (SELECT 1 AS *c3*, 2 AS c2 UNION SELECT 2 AS c2, 1 AS c1) AS x; ERROR: column "c1" does not exist LINE 1: SELECT c1,c2 FROM (SELECT 1 AS c3, 2 AS c2 UNION SELECT 2 AS... ^ But if you change the column names in the second SELECT in the UNION this is ignored: # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS *c3*, 1 AS *c4*) AS x; c1 | c2 + 1 | 2 2 | 1 Apparently, in a UNION the column names are derived from the first statement only. Postgresql 8.4.5 /Fredric <> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Just 2 days left to register for PGDay.EU 2010
Yup, you heard correctly - there are just two (and a bit) days left to register for the annual European PostgreSQL Conference, pgDay.EU 2010, being held in Stuttgart on December 6th and 7th, with a day of training sessions on the 8th. http://2010.pgday.eu/ With over 40 talks in a mix of English and German, this is an event not to be missed if you're a PostgreSQL user, developer, hobbyist, or are considering a deployment. There are a wide range of topics including talks on GIS, interoperability and migration, high availability and monitoring, business around PostgreSQL and case studies, as well as more academic topics. On day three we have a number of training courses available, including a two part course on PostGIS, presented by one of the leading developers, Mark Cave-Ayland from Sirius, deployment of applications in the Cloud with Servoy presented by Robert Ivens from Roclasi, and a two part PostgreSQL administration course (in German) given by Andreas Scherbaum for EnterpriseDB. Finally, as attendees from previous PGDay's will have come to expect, EnterpriseDB will be hosting a party for everyone on Monday night - definitely not one to miss! So, talk to the boss, fill out those pesky travel requisition forms, and head on over to the registration page! http://2010.pgday.eu/register -- Dave Page PostgreSQL Europe http://www.postgresql.eu/ -- 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] alter table add column - specify where the column will go?
* Grzegorz Jaśkiewicz: > 2010/11/24 Florian Weimer : >> * Grzegorz Jaśkiewicz: >> >>> just never use SELECT *, but always call columns by names. You'll >>> avoid having to depend on the order of columns, which is never >>> guaranteed, even if the table on disk is one order, the return columns >>> could be in some other. >> >> This can't be true because several SQL features rely on deterministic >> column order. Here's an example: >> >> SELECT 1 AS a, 2 AS b UNION SELECT 3 AS b, 4 AS a; >> >> a | b >> ---+--- >> 1 | 2 >> 3 | 4 >> (2 rows) > Yes, most DBs do a good job to keep it consistent, but they don't have > to. So unless you specify column names explicitly (like you did in the > example above), there's no guarantees. If the database looked at the column names, the result would be (1, 2), (4, 3), not (1, 2), (3, 4). -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] alter table add column - specify where the column will go?
2010/11/24 Florian Weimer : > * Grzegorz Jaśkiewicz: > >> just never use SELECT *, but always call columns by names. You'll >> avoid having to depend on the order of columns, which is never >> guaranteed, even if the table on disk is one order, the return columns >> could be in some other. > > This can't be true because several SQL features rely on deterministic > column order. Here's an example: > > SELECT 1 AS a, 2 AS b UNION SELECT 3 AS b, 4 AS a; > > a | b > ---+--- > 1 | 2 > 3 | 4 > (2 rows) > Read again what I wrote please. Yes, most DBs do a good job to keep it consistent, but they don't have to. So unless you specify column names explicitly (like you did in the example above), there's no guarantees. Most people struggle with long table names in joins and stuff, for instance: SELECT foo.one, bar.two FROM foo join ... Because they forget about the aliases, like SELECT a.one, b.two FROM foo a JOIN bar b .. -- GJ -- 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] alter table add column - specify where the column will go?
* Grzegorz Jaśkiewicz: > just never use SELECT *, but always call columns by names. You'll > avoid having to depend on the order of columns, which is never > guaranteed, even if the table on disk is one order, the return columns > could be in some other. This can't be true because several SQL features rely on deterministic column order. Here's an example: SELECT 1 AS a, 2 AS b UNION SELECT 3 AS b, 4 AS a; a | b ---+--- 1 | 2 3 | 4 (2 rows) -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming processing of result sets
Unless you use COPY, libpq loads the complete query result into memory. In some cases, this is not desirable. I know that with non-MVCC databases, it is important to load the result from the database server in a non-blocking fashion because you can easily stall other transactions or even deadlock if you block during result processing (waiting for another network connection, for instance). Is this true for PostgreSQL as well, or can clients block without causing too much trouble? -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] alter table add column - specify where the column will go?
On 2010-11-24 10.43, Thomas Kellerer wrote: Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. SELECT * also makes the code harder to read since you have to lookup the table definition to see what it'll return. You'll also be wasting resources to handle the data you'll never use. Maybe it doesn't matter for one resultset in one call but it quickly adds up. (This is something ORMs usually are very bad at.) I always try to convince people of this as well, but when they ask me under which circumstances this could happen, I can't think of a proper example. select * from ta join tb on ta.id=tb.aid; Add another column to "ta" and you get a different resultset. In general if you do any changes to your schema you need to go watch out for code using SELECT * since it easily breaks. For example if I do something like this in Python it will break if I add another column: a, b, c = resultset.next() Had I used "SELECT a, b, c" it wouldn't. It's a lousy example but not that uncommon. Does anybody have an example that would show this? I still don't have an example of when the internal ordering of a tables column could change. Anyhow, "SELECT *" is bad practice leading to error prone code and wasting resources. My 2c, /r Regards Thomas -- Regards, Robert "roppert" Gravsjö -- 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] alter table add column - specify where the column will go?
It is easy to create view based on SELECT which explicitly specifies the columns names. IMO it is better to hide tables structures behind views and work with them, since views are not materialized and it is easy to drop and recreate or just add another view into the database. With this approach you can than SELECT * FROM my_view without care of physical ordinal positions in a tables. 2010/11/24 Grzegorz Jaśkiewicz > just never use SELECT *, but always call columns by names. You'll > avoid having to depend on the order of columns, which is never > guaranteed, even if the table on disk is one order, the return columns > could be in some other. > -- // Dmitriy.
[GENERAL] FTS is taking "
I found that FTS is taking "') as c;
c
---
(1 row)
select * from ts_debug('english', '') as c ;
alias | description | token | dictionaries | dictionary | lexemes
---+-+--+--++-
tag | XML tag | | {} | |
I need to parse the statement "RMAN is not a DBA".
How can I do this?
Anye idea please.
Re: [GENERAL] alter table add column - specify where the column will go?
On Wed, Nov 24, 2010 at 09:37:02AM +, Grzegorz Jaśkiewicz wrote: > just never use SELECT *, but always call columns by names. You'll > avoid having to depend on the order of columns, which is never > guaranteed, even if the table on disk is one order, the return columns > could be in some other. People have been saying that on this list forever, and I agree you shouldn't *depend* on column order, but why does INSERT syntax allow you to omit the column names? INSERT INTO sometable VALUES (1, 2, 3); If columns inherently don't have an ordering, this shouldn't be possible because it would make no sense. Things like this INSERT syntax and the fact that columns are always returned in the same order when you "SELECT *" or when you check the table definition with \d condition people to expect being able to influence the order of columns. Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth -- 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] Debug advice when postgres connection maxing out
On 2010-11-23 20.56, anypossibility wrote: Thank you for your advice.I reviewed the query and it is the most simple one column value update with primary key query. I would like to share this with you and would like to receive advice as to whether I am on the right track. Facts: the connection maxed out and i could not even terminate postgres with SIGINT. I was afraid to do SIGQUIT so restarted the server itself (not sure if this was better decision) the server has been running just fine until a few days ago. No hardware update. Other servers that has exactly same spec (code, version, hardware) is having no issue... from this facts, I am leaning towards hardware issue.. though I have no idea where to start... This started to happen on one server a few days ago. So far this happens once a day. No pattern what's so ever in terms of client request, time of the day... Anything interesting in postgresql.log? Maybe you have to increase logging to find anything. Perhaps enable log_min_duration_statement to see if there are any long running statements that could give you a hint. For details see http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html My observation: From the look of output from the ps -ef | grep postgres (pasted below) what was happening is that postgres is not returning result or waiting for something. and that one process is holding up the rest of the process - basically nothing is processed yet postgres continue to receive request until it reaches to max connection. Where can I start to diagnose this issue? Any advice would be appreciated. --- please note: Actual IP was replaced with Client_IP SQL:~ root# ps -ef | grep postgres 502 891 1 0 0:05.61 ?? 0:06.54 /Library/PostgresPlus/8.3/bin/postgres -D /data 502 892 891 0 0:01.41 ?? 0:01.98 postgres: logger process 502 894 891 0 0:17.91 ?? 0:27.16 postgres: writer process 502 895 891 0 0:05.43 ?? 0:06.88 postgres: wal writer process 502 896 891 0 0:01.59 ?? 0:03.26 postgres: autovacuum launcher process 502 897 891 0 1:09.83 ?? 1:35.88 postgres: stats collector process 502 1007 891 0 2:10.40 ??33:38.91 postgres: DBA DB_Name Client_IP(60096) UPDATE I would be curious about this process since it stands out by the amount of time it been running. I would watch for a similar long running process and try to see what pg_stat_activity and pg_lock says about it. What hardware are you running on and what size database? Could it be you have a very large table on slow hardware and some client is trying to update all of that table? Any other services running on the same host? Could it be a shared storage used by some other host? /r 502 1008 891 0 0:00.82 ?? 0:20.91 postgres: DBA DB_Name Client_IP(60097) UPDATE 502 45397 891 0 0:00.01 ?? 0:00.11 postgres: DBA DB_Name Client_IP(64007) SELECT 502 45398 891 0 0:00.06 ?? 0:00.59 postgres: DBA DB_Name Client_IP(64008) idle 502 45399 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64009) SELECT 502 45400 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64012) SELECT 502 45401 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64013) SELECT 502 45402 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64014) SELECT 502 45403 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64015) SELECT 502 45404 891 0 0:00.01 ?? 0:00.03 postgres: DBA DB_Name Client_IP(64016) SELECT 502 45405 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64017) SELECT 502 45406 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64018) UPDATE 502 45407 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64019) SELECT 502 45408 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64020) SELECT 502 45409 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64021) SELECT 502 45410 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64022) SELECT 502 45411 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64023) SELECT 502 45412 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64024) SELECT 502 45413 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64025) SELECT 502 45414 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64026) SELECT 502 45415 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64027) UPDATE 502 45416 891 0 0:00.00 ?? 0:00.01 postgres: DBA DB_Name Client_IP(64028) SELECT 502 45417
[GENERAL] Optimizing query
Hello.
I have a query which works a bit slow.
It's runned on desktop computer: AMD Athlon X2 2GHz , Win Xp sp2, 1GB ram.
Postgres 8.4.5 with some changes in config:
shared_buffers = 200MB # min 128kB
# (change requires restart)
temp_buffers = 8MB # min 800kB
work_mem = 12MB # min 64kB
maintenance_work_mem = 32MB # min 1MB
Indexes in table "NumeryA":
"NTA", "NKA", "KodBłędu", "Plik"primary key
"DataPliku", "KodBłędu" index dp_kb
"NKA", "NTA"index nka_nta
Indexes in table "Rejestr stacji do naprawy":
"LP"- primary key
"Numer kierunkowy", substr("Numer stacji"::text, 1, 5) - index "3"
"Data weryfikacji" - index "Data weryfikacji_1"
"Numer kierunkowy", "Numer stacji", "Data odrzucania bilingu z
Serat" - index "Powtórzenia"
-
Query is:
--
SELECT
A."NKA",
A."NTA",
Min("PołączeniaMin") || ',' || Max("PołączeniaMax") AS "Biling",
Sum("Ile")::text AS "Ilość CDR",
R."LP"::text AS "Sprawa",
(R."Osoba weryfikująca") AS "Osoba",
to_char(min("Wartość"),'FM990D00') AS "Wartość po kontroli",
max(R."Kontrola po naprawie w Serat - CDR")::text AS "CDR po kontroli",
min(A."KodBłędu")::text AS KodBłędu,
Max(to_char(R."Data kontroli",'-MM-DD')) AS "Ostatnia Kontrola"
, max("Skutek wprowadzenia błednej ewidencji w Serat") as "Skutek"
, sum(www.a_biling_070("NRB"))::text
, sum(www.a_biling_darmowy("NRB"))::text
FROM
(SELECT "NumeryA".*
FROM ONLY "NumeryA"
WHERE "DataPliku" >= current_date-4*30 and "KodBłędu"=74::text
) AS A
LEFT JOIN
(SELECT * FROM "Rejestr stacji do naprawy"
WHERE "Data weryfikacji" >= current_date-4*30
) AS R
ON
A."NKA" = R."Numer kierunkowy"
and substr(A."NTA",1,5) = substr(R."Numer stacji",1,5)
and A."NTA" like R."Numer stacji"
GROUP BY R."Osoba weryfikująca",R."LP",A."NKA", A."NTA"
ORDER BY Sum("Ile") DESC
LIMIT 5000
--
Explain analyze:
--
"Limit (cost=30999.84..31012.34 rows=5000 width=149) (actual
time=7448.483..7480.094 rows=5000 loops=1)"
" -> Sort (cost=30999.84..31073.19 rows=29341 width=149) (actual
time=7448.475..7459.663 rows=5000 loops=1)"
"Sort Key: (sum("NumeryA"."Ile"))"
"Sort Method: top-N heapsort Memory: 1488kB"
"-> GroupAggregate (cost=11093.77..29050.46 rows=29341
width=149) (actual time=4700.654..7377.762 rows=14225 loops=1)"
" -> Sort (cost=11093.77..11167.12 rows=29341
width=149) (actual time=4699.587..4812.776 rows=46732 loops=1)"
"Sort Key: "Rejestr stacji do naprawy"."Osoba
weryfikująca", "Rejestr stacji do naprawy"."LP", "NumeryA"."NKA",
"NumeryA"."NTA""
"Sort Method: quicksort Memory: 9856kB"
"-> Merge Left Join (cost=8297.99..8916.58
rows=29341 width=149) (actual time=2931.449..3735.876 rows=46732
loops=1)"
" Merge Cond: ((("NumeryA"."NKA")::text =
("Rejestr stacji do naprawy"."Numer kierunkowy")::text) AND
((substr(("NumeryA"."NTA")::text, 1, 5)) = (substr(("Rejestr stacji do
naprawy"."Numer stacji")::text, 1, 5"
" Join Filter: (("NumeryA"."NTA")::text ~~
("Rejestr stacji do naprawy"."Numer stacji")::text)"
" -> Sort (cost=6062.18..6135.53 rows=29341
width=95) (actual time=2131.297..2241.303 rows=46694 loops=1)"
"Sort Key: "NumeryA"."NKA",
(substr(("NumeryA"."NTA")::text, 1, 5))"
"Sort Method: quicksort Memory: 7327kB"
"-> Bitmap Heap Scan on "NumeryA"
(cost=1502.09..3884.98 rows=29341 width=95) (actual
time=282.570..1215.355 rows=46694 loops=1)"
" Recheck Cond: (("DataPliku" >=
(('now'::text)::date - 120)) AND (("KodBłędu")::text = '74'::text))"
" -> Bitmap Index Scan on dp_kb
(cost=0.00..1494.75 rows=29341 width=0) (actual time=281.991..281.991
rows=46694 loops=1)"
"Index Cond: (("DataPliku"
>= (('now'::text)::date - 120)) AND (("KodBłędu")::text =
'74'::text))"
" -> Sort (cost=2235.82..2285.03 rows=19684
width=64) (actual time=800.101..922.463 rows=54902 loops=1)"
"Sort Key: "Rejestr stacji do
naprawy"."Numer kierunkowy", (substr(("Rejestr stacji do
naprawy"."Numer stacji")::text, 1, 5))"
"Sort Method: quicksort Memory: 3105kB"
"-> Seq Scan on "Rejestr stacji do
naprawy" (cost=0.00..831.88 rows=19684 width=64) (actual
time=2.118..361.463 rows=19529 loops=1)"
" Filter: ("Data weryfikacji" >=
(('now'::text)::date - 120))"
"Total runtime: 7495.697 ms"
-
How to make it faster ?
pasman
--
Re: [GENERAL] alter table add column - specify where the column will go?
Grzegorz Jaśkiewicz, 24.11.2010 10:37: just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. I always try to convince people of this as well, but when they ask me under which circumstances this could happen, I can't think of a proper example. Does anybody have an example that would show this? Regards Thomas -- 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] alter table add column - specify where the column will go?
just never use SELECT *, but always call columns by names. You'll avoid having to depend on the order of columns, which is never guaranteed, even if the table on disk is one order, the return columns could be in some other. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting current and average on a single row
What is best practice when extracting both current and average from a table? Demonstration table here contains data from a cheap weather station. I can obviously get the current reading like this: select temp_out, dewpoint from weather where datetime between (now() - '10 minutes'::interval) and now() order by datetime desc limit 1; and I can get averages like this: select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now(); In both cases there are a dozen or so columns in total. How are these best merged to yield a single row? Some form of join, or window functions? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] alter table add column - specify where the column will go?
Please note, that in cases when you can't do simple dump - fix - restore (e.g. in production) you can always create view(s) with ordinal positions of columns convenient for you. 2010/11/24 Dmitriy Igrishin > Hey Alexander, > > Ordinal positions of columns can't be set manually by ALTER TABLE. > > 2010/11/24 Alexander Farber > > Hello, >> >> is there a syntax to add a column not at the last place, but >> somewhere inbetween or do I have to dump/restore the table? >> >> For example if I'd like to add last_logout right after last_login: >> >> \d pref_users; >>Table "public.pref_users" >> Column |Type | Modifiers >> +-+--- >> id | character varying(32) | not null >> first_name | character varying(32) | >> last_name | character varying(32) | >> female | boolean | >> avatar | character varying(128) | >> city | character varying(32) | >> lat| real| >> lng| real| >> last_login | timestamp without time zone | default now() >> last_ip| inet| >> medals | smallint| default 0 >> >> Thank you >> Alex >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > > > -- > // Dmitriy. > > > -- // Dmitriy.
Re: [GENERAL] alter table add column - specify where the column will go?
Alexander Farber, 24.11.2010 08:49: Why do you want to do anything like that? Easier to read... login, logout I understand the "easier to read" part. But what do you mean with "login, logout"? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
