[SQL] backup and restore
Hello, I have two databases db1 and db2 with the same database structure on different systems with no network connection. In the first system with the db1 database user updates the master data. At the end of every day, the user needs to take the backup of updated data of master tables on db1 and update the data on another system with db2 database. We can't use WAL as in this as we want to take incremental backup of few tables only and can't use slony as there is no network connection between the systems. Please suggest some solution. Thanks, Jyoti -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] RAISE NOTICE
Hello everyone... I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE can't be shown on windows with a popup message like the EXCEPTION, indeed it goes to log messages list.. So, is there any way to show a popup message with a custom textmessage on it from a PL/PgSQL function?... Thanks to all in advance, Ciao Luigi
Re: [SQL] Distinct oddity
am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com: > On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania > wrote: >> Hi there, >> >> does this look right? >> >> FAKDB=# select count(distinct(f.land)) from firmen f where >> f.typlist='Redaktion'; >> count >> --- >> 1975 >> (1 row) >> >> FAKDB=# select count(distinct(f.land||'1')) from firmen f where >> f.typlist='Redaktion'; >> count >> --- >> 4944 >> (1 row) > > Yeah, that does seem odd. Could it be something like nulls in your > data set? just guessing really. If you could make a small test case > that shows it happening and allows others to reproduce it you're > likely to get more bites. It doesn't seem to be related to null values (which wouldn't explain it anyway) nor to this particular field... FAKDB=# select count(*) from firmen where bezeichnung is null; count --- 0 (1 row) FAKDB=# select count(distinct(f.bezeichnung)) from firmen f; count --- 72698 (1 row) FAKDB=# select count(distinct(f.bezeichnung||'e')) from firmen f; count --- 72892 (1 row) My attempts at reproducing this with a freshly created table failed, of course. FAKDB=# create table concattest(mytext text); CREATE TABLE FAKDB=# insert into concattest (mytext) select(generate_series(1,1)::text); INSERT 0 1 FAKDB=# insert into concattest (mytext) select(generate_series(1,1)::text); INSERT 0 1 FAKDB=# select count(distinct(mytext)) from concattest; count --- 1 (1 row) FAKDB=# select count(distinct(mytext||'2')) from concattest; count --- 1 (1 row) best, Maximilian Tyrtania -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and restore
Wow, I'm impressed. Let me quote part of the message you just replied to with a TOTALLY UNRELATED NEW THREAD: "First: Please don't reply to an existing message to create a new thread. Your mail client copies the replied-to message ID into the References: header, and well-implemented mail clients will thread your message under a now-unrelated thread. Compose a new message instead." -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and restore
Craig Ringer wrote: ... something kinda rude, in retrospect. Sorry. Unpleasantness is going around in my immediate environment, and I'm apparently prickly and grumpy. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and restore
Jyoti Seth wrote: > Hello, > > I have two databases db1 and db2 with the same database structure on > different systems with no network connection. In the first system with the > db1 database user updates the master data. At the end of every day, the user > needs to take the backup of updated data of master tables on db1 and update > the data on another system with db2 database. > > We can't use WAL as in this as we want to take incremental backup of few > tables only and can't use slony as there is no network connection between > the systems. > > Please suggest some solution. > > Thanks, > Jyoti > > I guess you are triyng to avoid the 'pg_dump - save_to_floppy - walk_to_db2_place - pg_restore' pattern. Well, can you save `pg_dump' in some middle place? One place which machine1 and machine2 have access to? If so, you can kind of automate the job using two programs, one (in the master) who 'upload' (via ftp maybe, scp or whatever) in this middle place, ando other in the slave who 'download' and process the file using pg_restore. HTH Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Luigi N. Puleio wrote: > Hello everyone... > > I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE > can't be shown on windows with a popup message like the EXCEPTION, indeed it > goes to log messages list.. > So, is there any way to show a popup message with a custom textmessage on it > from a PL/PgSQL function?... > > Thanks to all in advance, > Ciao > Luigi > > > > That looks like a sql-client (pgAdmin, pgAccess,?) implementation feature, isnt? Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania wrote: > am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com: > >> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania >> wrote: >>> Hi there, >>> >>> does this look right? >>> >>> FAKDB=# select count(distinct(f.land)) from firmen f where >>> f.typlist='Redaktion'; >>> count >>> --- >>> 1975 >>> (1 row) >>> >>> FAKDB=# select count(distinct(f.land||'1')) from firmen f where >>> f.typlist='Redaktion'; >>> count >>> --- >>> 4944 >>> (1 row) >> >> Yeah, that does seem odd. Could it be something like nulls in your >> data set? just guessing really. If you could make a small test case >> that shows it happening and allows others to reproduce it you're >> likely to get more bites. > > It doesn't seem to be related to null values (which wouldn't explain it > anyway) nor to this particular field... > > FAKDB=# select count(*) from firmen where bezeichnung is null; > count > --- > 0 > (1 row) That's not the same field as in the original query. > My attempts at reproducing this with a freshly created table failed, of > course. Instead of trying to create a test case from scratch, isolate some rows that cause this, put them in another table, and then pg_dump that one table, cleaned as needed for privacy, here. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Sort ofthe message should come along a standalone application too when with an ADO component I do a Post() which calls the function...just the matter is I can't modify the application to manage a possibly notification...only the postgresql's function... That's why I'm asking about a possibility to get a notification from the function like it's an EXCEPTION... --- On Fri, 5/8/09, Gerardo Herzig wrote: From: Gerardo Herzig Subject: Re: [SQL] RAISE NOTICE To: "Luigi N. Puleio" Cc: pgsql-sql@postgresql.org Date: Friday, May 8, 2009, 11:34 AM Luigi N. Puleio wrote: > Hello everyone... > > I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE > can't be shown on windows with a popup message like the EXCEPTION, indeed it > goes to log messages list.. > So, is there any way to show a popup message with a custom textmessage on it > from a PL/PgSQL function?... > > Thanks to all in advance, > Ciao > Luigi > > > > That looks like a sql-client (pgAdmin, pgAccess,?) implementation feature, isnt? Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Luigi N. Puleio написа: > Sort ofthe message should come along a standalone application too when > with an ADO component I do a Post() which calls the function...just the > matter is I can't modify the application to manage a possibly > notification...only the postgresql's function... > That's why I'm asking about a possibility to get a notification from the > function like it's an EXCEPTION... [...] I believe the fine manual is clear about this (http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html): "Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 18 for more information." -- Milen A. Radev -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
The fact is I have in my postgresql.conf this: client_min_messages = notice but if I call the function which has RAISE NOTICE it doesn't return a notice to the clienti...indeed I see it in the logfile on server status... that's why I was asking if there is a way... maybe I could also have written wrong config for that parameter... --- On Fri, 5/8/09, Milen A. Radev wrote: From: Milen A. Radev Subject: Re: [SQL] RAISE NOTICE To: pgsql-sql@postgresql.org Date: Friday, May 8, 2009, 11:52 AM Luigi N. Puleio написа: > Sort ofthe message should come along a standalone application too when > with an ADO component I do a Post() which calls the function...just the > matter is I can't modify the application to manage a possibly > notification...only the postgresql's function... > That's why I'm asking about a possibility to get a notification from the > function like it's an EXCEPTION... [...] I believe the fine manual is clear about this (http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html): "Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 18 for more information." -- Milen A. Radev -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Luigi N. Puleio wrote: > --- On Fri, 5/8/09, Gerardo Herzig wrote: > > From: Gerardo Herzig > Subject: Re: [SQL] RAISE NOTICE > To: "Luigi N. Puleio" > Cc: pgsql-sql@postgresql.org > Date: Friday, May 8, 2009, 11:34 AM > > Luigi N. Puleio wrote: >> Hello everyone... >> >> I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE >> can't be shown on windows with a popup message like the EXCEPTION, indeed it >> goes to log messages list.. >> So, is there any way to show a popup message with a custom textmessage on it >> from a PL/PgSQL function?... >> >> Thanks to all in advance, >> Ciao >> Luigi >> >> >> >> > That looks like a sql-client (pgAdmin, pgAccess,?) implementation > feature, isnt? > > Gerardo > > Sort ofthe message should come along a standalone application too when with an ADO component I do a Post() which calls the function...just the matter is I can't modify the application to manage a possibly notification...only the postgresql's function... > That's why I'm asking about a possibility to get a notification from the function like it's an EXCEPTION... > Well, in that case, the ADO component is the client. This is the place to modify de code, to capture the NOTICE. Im shure the ADO (or any other component who actually connect to the database and makes the query) has the ability to react when an EXCEPTION is thrown. My guess is that EXCEPTION's goes trough StandardError, but NOTICE's goes trough StandardOutput, and that could be some idea why your app is not getting notice about NOTICES :) I will wait for some expert dude to respond this, because im going myself into unknown territory. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
Is firmen a table or a view? From: Scott Marlowe To: Maximilian Tyrtania Cc: pgsql-sql@postgresql.org Sent: Friday, May 8, 2009 5:35:21 AM Subject: Re: [SQL] Distinct oddity On Fri, May 8, 2009 at 3:28 AM, Maximilian Tyrtania wrote: > am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com: > >> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania >> wrote: >>> Hi there, >>> >>> does this look right? >>> >>> FAKDB=# select count(distinct(f.land)) from firmen f where >>> f.typlist='Redaktion'; >>> count >>> --- >>> 1975 >>> (1 row) >>> >>> FAKDB=# select count(distinct(f.land||'1')) from firmen f where >>> f.typlist='Redaktion'; >>> count >>> --- >>> 4944 >>> (1 row) >> >> Yeah, that does seem odd. Could it be something like nulls in your >> data set? just guessing really. If you could make a small test case >> that shows it happening and allows others to reproduce it you're >> likely to get more bites. > > It doesn't seem to be related to null values (which wouldn't explain it > anyway) nor to this particular field... > > FAKDB=# select count(*) from firmen where bezeichnung is null; > count > --- > 0 > (1 row) That's not the same field as in the original query. > My attempts at reproducing this with a freshly created table failed, of > course. Instead of trying to create a test case from scratch, isolate some rows that cause this, put them in another table, and then pg_dump that one table, cleaned as needed for privacy, here. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] performance question
Hi, All
Suppose you have a query like this
SELECT *
FROM t_sitesresumebydate a
JOIN t_triple_association c
ON "IDSiteResume" = "IDResume"
WHERE "dtDate" BETWEEN '2009-2-1'
AND '2009-2-3'
AND "IDHitsAccount" = 378284
With this time interval it completes in less than a second.
If I add one day to the condition, i.e., if it becomes BETWEEN '2009-2-1' AND
'2009-2-4'
the performance is radically different, it jumps to a staggering 424 seconds.
and the number of records returned is just 117 (against 79, by the former
condition)
Frankly, I cannot understand the reason for this, it seems the planner is
taking radically diferent plans on the two conditions (they are below).
I have an index on all the fields used in the query.
Can anyone help me in fixing this, please?
Thanks in advance for your kind help
Best,
Oliveiros
"Hash Join (cost=46644.50..751271.16 rows=117 width=60) (actual
time=15821.110..424120.924 rows=247 loops=1)"
" Hash Cond: (c."IDResume" = a."IDSiteResume")"
" -> Seq Scan on t_triple_association c (cost=0.00..554934.99 rows=29938099
width=32) (actual time=38.253..392251.754 rows=30101626 loops=1)"
" -> Hash (cost=46644.30..46644.30 rows=82 width=28) (actual
time=2711.356..2711.356 rows=23 loops=1)"
"-> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on
t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual
time=881.146..2711.303 rows=23 loops=1)"
" Index Cond: ("IDHitsAccount" = 378284)"
" Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <=
'2009-02-04'::date))"
"Total runtime: 424121.180 ms"
"Nested Loop (cost=108.43..745296.34 rows=79 width=60) (actual
time=44.283..311.942 rows=185 loops=1)"
" -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a
(cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17
loops=1)"
"Index Cond: ("IDHitsAccount" = 378284)"
"Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <=
'2009-02-03'::date))"
" -> Bitmap Heap Scan on t_triple_association c (cost=108.43..12658.83
rows=3515 width=32) (actual time=16.902..16.910 rows=11 loops=17)"
"Recheck Cond: (a."IDSiteResume" = c."IDResume")"
"-> Bitmap Index Scan on "index" (cost=0.00..108.43 rows=3515
width=0) (actual time=14.466..14.466 rows=11 loops=17)"
" Index Cond: (a."IDSiteResume" = c."IDResume")"
"Total runtime: 312.192 ms"
[SQL] ascii-betical sort order?
Is there some way to do ascii-betical sort ordering in postgres (i.e. capital letters come before lowercase)? It appears that text ordering is dictionary-alphabetical. It's useful, but it's different from the DBMS I'm porting some applications from (SQL Server, in case you were curious). It may not be terribly important, but it'd be useful to know in case it actually is an issue. I couldn't find any clear answer searching online. Thanks, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] performance question
"Oliveiros Cristina" writes:
> Frankly, I cannot understand the reason for this, it seems the planner is
> taking radically diferent plans on the two conditions (they are below).
Yup, and you seem to be near the crossover point where it thinks they
have equal cost. You need to be fixing the inaccurate cost estimates.
The most obvious problem is the bad rowcount estimate here:
> "-> Bitmap Index Scan on "index" (cost=0.00..108.43 rows=3515
> width=0) (actual time=14.466..14.466 rows=11 loops=17)"
> " Index Cond: (a."IDSiteResume" = c."IDResume")"
Perhaps increasing the statistics targets for one or both tables would
help on that.
Another odd thing is that essentially identical indexscans are taking
radically different times:
> "-> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on
> t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual
> time=881.146..2711.303 rows=23 loops=1)"
> " Index Cond: ("IDHitsAccount" = 378284)"
> " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <=
> '2009-02-04'::date))"
> " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a
> (cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17
> loops=1)"
> "Index Cond: ("IDHitsAccount" = 378284)"
> "Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <=
> '2009-02-03'::date))"
I think probably the second one was fast because the data is already
cached, so you're not making an entirely fair comparison. If your
expectation is that the database is going to be operating under mostly
cached conditions, then you probably ought to adjust the planner cost
parameters to reflect that (look at effective_cache_size, and try
reducing random_page_cost).
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ascii-betical sort order?
Peter Koczan writes: > Is there some way to do ascii-betical sort ordering in postgres (i.e. > capital letters come before lowercase)? It appears that text ordering > is dictionary-alphabetical. You're apparently using a non-C locale ... you need to re-initdb in C locale. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ascii-betical sort order?
On Fri, May 8, 2009 at 12:57 PM, Tom Lane wrote: > You're apparently using a non-C locale ... you need to re-initdb in > C locale. On Fri, May 8, 2009 at 12:58 PM, Kenneth Marshall wrote: > Try looking under Localization in the manual (Chapter 22). Excellent, just what I was looking for. I'll look at this when I have a bit more time. Thanks, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Distinct oddity
Maximilian Tyrtania writes: > am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marl...@gmail.com: >> Yeah, that does seem odd. Could it be something like nulls in your >> data set? just guessing really. If you could make a small test case >> that shows it happening and allows others to reproduce it you're >> likely to get more bites. > It doesn't seem to be related to null values (which wouldn't explain it > anyway) nor to this particular field... Can you reproduce it in other contexts than specifically count(distinct)? In particular I'd try select count(*) from (select distinct f.bezeichnung from firmen f) ss; select count(*) from (select distinct f.bezeichnung||'e' from firmen f) ss; If those give the same numbers as you're showing here, then the next step would be to dump out the actual results of the SELECT DISTINCT queries and compare them --- looking at the actual data values should give some insight as to what's happening. BTW, what is the datatype of f.bezeichnung, and what locale are you running in? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] view vs. tables used to define that view
May I know is it possible to query dictionary table(s) in order to return relationship between a view and table(s) please? For example, create view v1 as (select ... from T1 left join T2 ... ) Result: view Name | Table Name v1| T1 v1| T2 Thanks a lot! Lu Ying -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
