Re: [SQL] help!
On Wed, 16 Oct 2002 19:15, John Geng wrote: > how to migrate sql from MS sql server to postgresql? > i'd like to tranfer sql schema from MS server > Example: > ***1* > if exists (select * from sysobjects > where id = object_id(N'[admin].[test]') and > OBJECTPROPERTY(id, N'IsUserTable') = 1) > drop table [admin].[test] if exists (select * from pg_tables where tablename = 'http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] High Availability PostgreSQL solution?
[This is a re-post.. my initial attempt seemed to have been lost. Apologies for any duplication] We are looking to create a two-machine high-availability configuration for PostgreSQL. We have been pursuing using something like rserv, but this appears to be more of a slave sync tool. Actually, this is also important to us (as we expect we will need to scale to lots of readers doing queries on the database (we are running datamart of website performance metrics). But, rserv (and eRServer, its commercial cousin) dont seem to have a hot-standby capability at the moment. I have looked through some of the PostgreSQL archives and I've seen articles about other approaches, but I mostly see replication engines (which are a good thing, but not what I am hunting for). I saw one posting that suggested using a clustered filesystem; is this an option? Has anyone done postgreSQL on a replicating/clustered file system with decent performance? Any and all ideas are welcome. Thanks in advance, Charlie -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help on creating a High Availability PostgreSQL
> We are looking to create a two-machine high-availability configuration > for PostgreSQL. We have been pursuing using something like rserv, but > this appears to be more of a slave sync tool. Actually, this is also > important to us (as we expect we will need to scale to lots of readers > doing queries on the database (we are running datamart of website > performance metrics). > > But, rserv (and eRServer, its commercial cousin) dont seem to have a > hot-standby capability at the moment. I have looked through some of the > PostgreSQL archives and I've seen articles about other approaches, but I > mostly see replication engines (which are a good thing, but not what I > am hunting for). > > I saw one posting that suggested using a clustered filesystem; is this > an option? Has anyone done postgreSQL on a replicating/clustered file > system with decent performance? Any and all ideas are welcome. We are selling Lifekeeper (a hot-standby cluster software) + PostgreSQL adapter software for Lifekeeper + support combo package called "PostgreSQL HA package" in Japan. This requires an expensive shared disk hardware though. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER the result of a query by date
Title: RE: [SQL] ORDER the result of a query by date andres javier garcia garcia wrote: > Hello; > I've got the result of a query that appears as: > cod_variable | cod_station | year | month | day | rain > -+---++--+ > --+-- > 30201 | 7237 | 1953 | 1 | 1 | 2 > 30201 | 7237 | 1953 | 2 | 1 | 5 > 30201 | 7237 | 1953 | 3 | 1 | 0 > 30201 | 7237 | 1953 | 4 | 1 | -3 . > > (Of course, thanks to Stephan Szabo for the method to obtain this > from my strange source data. I didn't think this was possible.) > > After have done this query I've realized that I need the data to be > ordered by date. Do you have any suggestion? > > Best regards > -- > Javier > ORDER BY year,month,day should do it. hth, - Stuart
Re: [SQL] Sum of Every Column
Hi tom.
In postgresql you cannot have functions with
a variable number of parameters.
(pgsql supports some kind of method overloading
based on the type and number of parameters,
thats the reason why).
But if you run Unix (with freebsd and linux it is trivial
as you will see, with solaris you have to be more formal)
there is a hack.
I had the same problem as yours, but in my case
i wanted the minimum, maximum of double numbers (maximum 14 of them).
In BSD,linux you can navigate the process' stack with no problems.
The idea is to write a variable parameter number function,
with the first parameter denoting the number of the rest
parameters, (just like printf)
and then define a pgsql function with the maximum number
of parameters that you will ever have.
I attach the code (tested under RedHat 7.1, kernel 2.4.7,
glibc-2.2.2-10,gcc-2.96-81 and FreeBSD 4.6.1-RC2, both with
postgresql 7.2.1),
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email: [EMAIL PROTECTED]
[EMAIL PROTECTED]
#include
#include
int minihack(argc)
int argc;
{
int i;
int temp = *(&argc + 1);
int tval;
for (i=1;i temp)
temp = tval;
}
return temp;
}
int4 maxi(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14)
int4 t1;
int4 t2;
int4 t3;
int4 t4;
int4 t5;
int4 t6;
int4 t7;
int4 t8;
int4 t9;
int4 t10;
int4 t11;
int4 t12;
int4 t13;
int4 t14;
{
return maxihack(14,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14);
}
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 'next' or similar in plpgsql
> > In perl we have 'next' function to skip rest of the statements in the loop > and to start with next iteration. In plpgsql, do we have something > similar? How do we skip rest of the statements in a loop in plpgsql? > The only statement which can be used in a probably tricky way seems to be EXIT [ label ] [ WHEN expression ]; What you are really looking for is something like the C statement "continue;" I'm quite surprised this is not available in plpgsql. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Basic question.
Hi; I'm sorry for ask this basic question. But I can't find in the documentation. I'm connected to a database; and how can I execute a query that I've got in a file? And. If I'm not connected to any database. Can I execute a file that makes a query on a database? Thanks - Javier ---(end of broadcast)--- TIP 3: 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: [SQL] Basic question.
Uz.ytkownik andres javier garcia garcia napisa?: Hi; I'm sorry for ask this basic question. But I can't find in the documentation. I'm connected to a database; and how can I execute a query that I've got in a file? And. If I'm not connected to any database. Can I execute a file that makes a query on a database? But how are you connected to database? If you are using tool named "psql" you can use "\i filename" or you can do from command line psql -U Tomasz Myrta ---(end of broadcast)--- TIP 3: 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: [SQL] Basic question.
And. If I'm not connected to any database. Can I execute a file that makes a query on a database? Sorry, I made a mistake psql -f -U I'm not sure, but you can add export PGPASSWORD= before this command if you need a password. Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] sub-select trouble: wrong SQL or PostgreSQL issue?
Thank you both for your prompt responses. Bug #526 (as Tom pointed out) does look very similar to my problem. I'll attempt to upgrade my development PostgreSQL and eventually my production servers if all goes well. Thanks again, On Wed, Oct 23, 2002 at 11:28:01PM -0400, Tom Lane wrote: > >> Is it possible that the sub-selects are somehow > >> affecting the result sets? > > > I think you want to upgrade, 7.3beta at least gives your > > expected output on all the queries when I tried it. I don't > > currently have a 7.2.* system to test on here to try it > > with that series. > > I think this may be the same problem as bug #526, > http://archives.postgresql.org/pgsql-bugs/2001-11/msg00168.php > If so, the fix is in 7.2.*. > > regards, tom lane sidster -- They who would sacrifice freedom for security will have neither. -Ben Franklin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sub-select with aggregate
Uz.ytkownik Stephan Szabo napisa?: I see these two queries that in 7.3 push the clause into the subquery and I believe should have the same output: create view v as select group_id, a/sum_a as percent_a, b/sum_b as percent_b from (select group_id, sum(a) as sum_a, sum(b) as sum_b from users group by group_id) X join users using (group_id); and create view v as select X.group_id, a/sum_a as percent_a, b/sum_b as percent_b from users join (select group_id, sum(a) as sum_a, sum(b) as sum_b from users group by group_id) X on (X.group_id=users.group_id); I made tests of these queries (Postgres 7.2). In my database there are master table "bilety" and detail "przejazdy": 1) Exposing field from sub-query drop view v; create view v as select X.id_biletu, netto, vat from bilety B join (select id_biletu, sum(netto) as netto, sum(vat) as vat from przejazdy group by id_biletu) X on (X.id_biletu=B.id_biletu); explain select * from v where id_biletu=12345; Nested Loop (cost=0.00..29.58 rows=5 width=24) -> Subquery Scan x (cost=0.00..7.94 rows=1 width=20) -> Aggregate (cost=0.00..7.94 rows=1 width=20) -> Group (cost=0.00..7.93 rows=1 width=20) -> Index Scan using qq2 on przejazdy (cost=0.00..7.92 rows=1 width=20) -> Index Scan using ind_bil_id on bilety b (cost=0.00..21.58 rows=5 width=4) 2) Exposing field from master-table drop view v; create view v as select X.id_biletu, netto, vat from bilety B join (select id_biletu, sum(netto) as netto, sum(vat) as vat from przejazdy group by id_biletu) X on (X.id_biletu=B.id_biletu); explain select * from v where id_biletu=12345; Merge Join (cost=4595.39..4627.36 rows=3 width=24) -> Index Scan using ind_bil_id on bilety b (cost=0.00..21.55 rows=5 width=4) -> Sort (cost=4595.39..4595.39 rows=4146 width=20) -> Subquery Scan x (cost=0.00..4346.25 rows=4146 width=20) -> Aggregate (cost=0.00..4346.25 rows=4146 width=20) -> Group (cost=0.00..4138.93 rows=41463 width=20) -> Index Scan using qq2 on przejazdy (cost=0.00..4035.28 rows=41463 width=20) 3) Using planner to choose exposed field drop view v; create view v as select id_biletu, netto, vat from bilety B join (select id_biletu, sum(netto) as netto, sum(vat) as vat from przejazdy group by id_biletu) X using (id_biletu); explain select * from v where id_biletu=12345; Merge Join (cost=4595.39..4627.36 rows=3 width=24) -> Index Scan using ind_bil_id on bilety b (cost=0.00..21.55 rows=5 width=4) -> Sort (cost=4595.39..4595.39 rows=4146 width=20) -> Subquery Scan x (cost=0.00..4346.25 rows=4146 width=20) -> Aggregate (cost=0.00..4346.25 rows=4146 width=20) -> Group (cost=0.00..4138.93 rows=41463 width=20) -> Index Scan using qq2 on przejazdy (cost=0.00..4035.28 rows=41463 width=20) Now I know the solution - to speed up this query I have to manually expose field from sub-query. It works fine, but I still don't know why I can't do this opposite way. Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] sub-select with aggregate
Sorry, In second query is: drop view v; create view v as select B.id_biletu... Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problems with to_char(created, 'WW')
Hi. I have the following schema in PostgreSQL-7.2.2: CREATE TABLE session ( session_id varchar(256) NOT NULL PRIMARY KEY, created timestamp DEFAULT 'now' NOT NULL, last_accessed timestamp NOT NULL, destroyed timestamp NOT NULL, username varchar -- Allow sessions from not logged in users ); Now I run this query to extract the week numbers: janerik=# select created, to_char(created, 'WW') as week from session WHERE username IS NULL ORDER BY week; created | week +-- 2002-09-24 11:23:21.206+02 | 39 2002-09-24 18:19:06.304+02 | 39 2002-09-25 10:50:27.139+02 | 39 2002-09-30 12:32:06.898+02 | 39 2002-10-01 16:26:41.122+02 | 40 2002-10-04 13:47:02.922+02 | 40 2002-10-04 21:28:13.409+02 | 40 2002-10-04 17:35:50.954+02 | 40 2002-10-04 23:31:27.566+02 | 40 2002-10-04 23:34:18.286+02 | 40 2002-10-07 13:48:14.275+02 | 40 2002-10-07 13:50:04.352+02 | 40 2002-10-07 14:10:01.441+02 | 40 2002-10-11 12:57:53.458+02 | 41 2002-10-11 13:24:49.124+02 | 41 2002-10-13 16:26:52.546+02 | 41 2002-10-14 23:50:51.131+02 | 41 2002-10-15 14:54:12.341+02 | 42 2002-10-15 15:09:36.84+02 | 42 2002-10-15 15:21:26.59+02 | 42 2002-10-20 12:14:05.203+02 | 42 2002-10-20 20:19:44.309+02 | 42 2002-10-21 14:23:31.425+02 | 42 2002-10-22 12:12:31.63+02 | 43 2002-10-23 14:00:18.478+02 | 43 (25 rows) Now - my question is, why is monday 21. in week 42, but tuesday 22. in week 43? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> - There are 10 kinds of people in the world, those that can do binary arithmetic and those that can't. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ORDER the result of a query by date
Hello; I've got the result of a query that appears as: cod_variable | cod_station | year | month | day | rain -+---++--+--+-- 30201 | 7237 | 1953 |1 | 1 |2 30201 | 7237 | 1953 |2 | 1 |5 30201 | 7237 | 1953 |3 | 1 |0 30201 | 7237 | 1953 |4 | 1 | -3 . (Of course, thanks to Stephan Szabo for the method to obtain this from my strange source data. I didn't think this was possible.) After have done this query I've realized that I need the data to be ordered by date. Do you have any suggestion? Best regards -- Javier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problems with to_char(created, 'WW')
On Thu, Oct 24, 2002 at 12:51:35PM +0200, Andreas Joseph Krogh wrote: > > janerik=# select created, to_char(created, 'WW') as week from session WHERE > username IS NULL ORDER BY week; Please, see docs and 'IW' (ISO week). Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Problems with to_char(created, 'WW')
Uz.ytkownik Andreas Joseph Krogh napisa?: Now - my question is, why is monday 21. in week 42, but tuesday 22. in week 43? Read the documentation - 4.7. Data Type Formatting Functions "WW - week number of year (1-53) where first week start on the first day of the year" Year 2002 started on Tuesday, so monday is still week 42, but tuesday is week 43. Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER the result of a query by date
On Thu, 24 Oct 2002, andres javier garcia garcia wrote: > Hello; > I've got the result of a query that appears as: > cod_variable | cod_station | year | month | day | rain > -+---++--+--+-- > 30201 | 7237 | 1953 |1 | 1 |2 > 30201 | 7237 | 1953 |2 | 1 |5 > 30201 | 7237 | 1953 |3 | 1 |0 > 30201 | 7237 | 1953 |4 | 1 | -3 > . > > (Of course, thanks to Stephan Szabo for the method to obtain this from my > strange source data. I didn't think this was possible.) > > After have done this query I've realized that I need the data to be ordered > by date. Do you have any suggestion? select ,year,month,day from order by year,month,day. Also take a look at date,timestamp data types. Note that you can process your initial "strange" source using tools as awk,perl,sh,C programs to format your datasets in sql insert statements or copy format. > > Best regards > -- > Javier > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Simulating a SELECT..FOR UPDATE to LOCK and SELECT statement
Ludwig Lim <[EMAIL PROTECTED]> writes: > Is there a way to emulate a SELECT..FOR UPDATE to > series of LOCK/SELECT statement. No. LOCK can only acquire table-level locks; the point of SELECT FOR UPDATE is to acquire row-level locks. >I am surprised because according to the docs > (version 7.2), it says: > ROW SHARE MODE > Note: Automatically acquired by SELECT ... FOR > UPDATE. SELECT FOR UPDATE gets a table-level lock just to ensure that the table doesn't disappear from under it while it's doing its scan. The ROW SHARE lock is pretty weak though, and doesn't block anything less drastic than DROP/ALTER TABLE or VACUUM FULL. The 7.3 development docs are perhaps clearer about this; try http://developer.postgresql.org/docs/postgres/explicit-locking.html regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Help on creating a High Availability PostgreSQL solution?
We are looking to create a two-machine high-availability configuration for PostgreSQL. We have been pursuing using something like rserv, but this appears to be more of a slave sync tool. Actually, this is also important to us (as we expect we will need to scale to lots of readers doing queries on the database (we are running datamart of website performance metrics). But, rserv (and eRServer, its commercial cousin) dont seem to have a hot-standby capability at the moment. I have looked through some of the PostgreSQL archives and I've seen articles about other approaches, but I mostly see replication engines (which are a good thing, but not what I am hunting for). I saw one posting that suggested using a clustered filesystem; is this an option? Has anyone done postgreSQL on a replicating/clustered file system with decent performance? Any and all ideas are welcome. Thanks in advance, Charlie -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] sub-select with aggregate
Stephan Szabo <[EMAIL PROTECTED]> writes: > Basically, as I understand it, > select * from a,b where a.a=b.a and a.a=3; > isn't going to realize that b.a=3 and act as if > you typed that. We have talked about adding code to make that deduction. The issue is how to do so without expending a lot of cycles (that will be wasted in every query where no improvement results, which is most of them). There are also some ticklish issues about side-effects of adding such clauses. in the above example, if we did filter b with b.a=3, then the join clause a.a=b.a becomes a no-op and does not reduce the number of rows; if we fail to account for that fact we will underestimate the number of rows out of the join, possibly pessimizing higher levels of plan. Also, we might as well implement the join as a nestloop; no percentage in fooling with hash or merge overhead. I have some thoughts about this, but it's not happening for 7.3 ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
