Re: [HACKERS] We have a launch abort ... PG update releases will be delayed
I wrote: (The exact new release date isn't determined yet.) Just FYI, the agreed plan is to slip the release schedule 3 days; public announcement will be Thursday not Monday. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump restore time and Foreign Keys
On Thursday 05 June 2008 08:56:35 Simon Riggs wrote:
On Thu, 2008-06-05 at 07:57 -0400, Andrew Dunstan wrote:
Simon Riggs wrote:
pg_dump restore times can be high when they include many ALTER TABLE
ADD FORIEGN KEY statements, since each statement checks the data to see
if it is fully valid in all cases.
I've been asked why we run that at all?, since if we dumped the
tables together, we already know they match.
If we had a way of pg_dump passing on the information that the test
already passes, we would be able to skip the checks.
Proposal:
* Introduce a new mode for ALTER TABLE ADD FOREIGN KEY [WITHOUT CHECK];
When we run WITHOUT CHECK, iff both the source and target table are
newly created in this transaction, then we skip the check. If the check
is skipped we mark the constraint as being unchecked, so we can tell
later if this has been used.
* Have pg_dump write the new syntax into its dumps, when both the
source and target table are dumped in same run
I'm guessing that the WITHOUT CHECK option would not be acceptable as
an unprotected trap for our lazy and wicked users. :-)
This whole proposal would be a major footgun which would definitely be
abused, IMNSHO.
OK, understood. Two negatives is enough to sink it.
Heh, I would have argued that the idea should go the other way and just make
this part of the normal syntax. Oracle DBA's have been doing this for years
(MS SQL supports it too actually) and it really helps working around having
to hold locks on large relations for lengthy periods of times. Heck, I'd like
to see a no check option for all constraints really.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump restore time and Foreign Keys
On Sat, 2008-06-07 at 13:08 -0400, Robert Treat wrote: On Thursday 05 June 2008 08:56:35 Simon Riggs wrote: On Thu, 2008-06-05 at 07:57 -0400, Andrew Dunstan wrote: Heh, I would have argued that the idea should go the other way and just make this part of the normal syntax. Oracle DBA's have been doing this for years (MS SQL supports it too actually) and it really helps working around having to hold locks on large relations for lengthy periods of times. Heck, I'd like to see a no check option for all constraints really. Interesting that SQL Server does it also. Holding the lock for a long period is just one more problem. :-) I'm always torn between the I-know-what-Im-doing-so-give-me-the-option viewpoint and the some-dumbass-will-abuse-it viewpoint. I see the results of both viewpoints daily. Perhaps we need a GUC that says expert_mode = on. In expert_mode we are allowed to do a range of things that are normally avoided - there would be an explicit list. Managers can then take a single considered decision as to whether the situation warrants extreme action and their DBA is good enough to handle it. That might resolve our continued angst about whether our users our smart enough to avoid the gotchas, or just smart enough to win a DBA's Darwin Award. The UNIX philosophy has always been to allow the power to exist, yet seek to minimise the number of people who exercise it. Another idea might be to make such command options superuser only, to ensure the power is available, yet only in the hands of, by-definition, the trusted few. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump restore time and Foreign Keys
Simon Riggs wrote: On Sat, 2008-06-07 at 13:08 -0400, Robert Treat wrote: On Thursday 05 June 2008 08:56:35 Simon Riggs wrote: On Thu, 2008-06-05 at 07:57 -0400, Andrew Dunstan wrote: Heh, I would have argued that the idea should go the other way and just make this part of the normal syntax. Oracle DBA's have been doing this for years (MS SQL supports it too actually) and it really helps working around having to hold locks on large relations for lengthy periods of times. Heck, I'd like to see a no check option for all constraints really. Interesting that SQL Server does it also. Holding the lock for a long period is just one more problem. :-) I'm always torn between the I-know-what-Im-doing-so-give-me-the-option viewpoint and the some-dumbass-will-abuse-it viewpoint. I see the results of both viewpoints daily. Perhaps we need a GUC that says expert_mode = on. In expert_mode we are allowed to do a range of things that are normally avoided - there would be an explicit list. Managers can then take a single considered decision as to whether the situation warrants extreme action and their DBA is good enough to handle it. That might resolve our continued angst about whether our users our smart enough to avoid the gotchas, or just smart enough to win a DBA's Darwin Award. The UNIX philosophy has always been to allow the power to exist, yet seek to minimise the number of people who exercise it. Another idea might be to make such command options superuser only, to ensure the power is available, yet only in the hands of, by-definition, the trusted few. If we go down this road then I would far rather we tried to devise some safe (or semi-safe) way of doing it instead of simply providing expert (a.k.a. footgun) mode. For instance, I'm wondering if we could do something with checksums of the input lines or something else that would make this difficult to do in circumstances other than pg_restore. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO, FAQs to Wiki?
Alvaro Herrera wrote: Bruce Momjian wrote: Magnus has started moving the Developer's FAQ to a wiki. I am thinking we should move the main FAQ and the TODO list to a wiki as well if the community is in agreement. Discussion with you and Magnus indicated that you were both committed to having the TODO on the wiki, but each was waiting on the other for anything to happen. Now that the PGCon dust has been settled for quite a while, should we proceed with that plan? Yes. I just haven't had time to do it. If somebody else has the time to do it, please go ahead (just post here to let me know so it's not double-worked). //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump restore time and Foreign Keys
Simon Riggs [EMAIL PROTECTED] writes: Perhaps we need a GUC that says expert_mode = on. ... Another idea might be to make such command options superuser only, to ensure the power is available, yet only in the hands of, by-definition, the trusted few. This all seems pretty useless, as the sort of user most likely to shoot himself in the foot will also always be running as superuser. I'd much rather see us expend more effort on speeding up the checks than open holes in the system. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO, FAQs to Wiki?
Magnus Hagander wrote: Alvaro Herrera wrote: Discussion with you and Magnus indicated that you were both committed to having the TODO on the wiki, but each was waiting on the other for anything to happen. Now that the PGCon dust has been settled for quite a while, should we proceed with that plan? Yes. I just haven't had time to do it. If somebody else has the time to do it, please go ahead (just post here to let me know so it's not double-worked). Did you use a script last time? If so, can you please post it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO, FAQs to Wiki?
Alvaro Herrera wrote: Magnus Hagander wrote: Alvaro Herrera wrote: Discussion with you and Magnus indicated that you were both committed to having the TODO on the wiki, but each was waiting on the other for anything to happen. Now that the PGCon dust has been settled for quite a while, should we proceed with that plan? Yes. I just haven't had time to do it. If somebody else has the time to do it, please go ahead (just post here to let me know so it's not double-worked). Did you use a script last time? If so, can you please post it? No. I found a tool on the web somewhere that did parts of it, then I just threw a bunch of ad-hoc sed statements on it. I can't find the link *or* the sed statements right now though :-( But they were fairly simple. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] math error or rounding problem Money type
I believe i have found a math bug/rounding problem with Money type when its used with SUM()... Postgresql 8.3.1 --- Background on the Problem We have gl_trans table with 92,000 rows with one column containing the positive and negative entries. In order to make this gl_trans table make more sense and to group the accounts in correct debits and credits along with type of accounts, A view was created that does grouping and sorting. To further make things easier the view casted the results into the Money Type just to make the select statements that call the view shorter. All looked great for several weeks till all of sudden the sumed values for all accounts goes out by 0.01. I needed to confirm this was a rounding problem and not a GL entry that was bad. ( if we had a bad entry this would scream we have a far bigger problem where the application allowed an GL entry to be committed that was out of balance) To confirm that all entries made have equal and opposite entry below select statement was created. The gltrans_sequence column is integer key that groups General Ledger entries together so all the sides of a specific entry can be found. select * from (select gltrans_sequence, sum(gltrans_amount) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg pos.pos*-1 This returns no records as expected... Now armed with that no entry was bad I suspected it had to be with the money data type. So I added explicit castings select * from (select gltrans_sequence, sum(gltrans_amount::text::money) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount::text::money) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg pos.pos*-1 select * from (select gltrans_sequence, sum(gltrans_amount::text::money) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount::text::money) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg::text::money pos.pos::text::money*-1 - select * from (select gltrans_sequence, sum(gltrans_amount) as Neg from gltrans where gltrans_amount 0 group by gltrans_sequence) as neg, (select gltrans_sequence, sum(gltrans_amount) as pos from gltrans where gltrans_amount 0 group by gltrans_sequence) as pos where neg.gltrans_sequence = pos.gltrans_sequence and neg.neg::text::money pos.pos::text::money*-1 --- Nothing resulted in showing a entry that was out of balance. --Identifying the problem --- So i turned my attention to the view which casted numeric type to Money. View is called trailbalance The Bad Select Statement that creates the View -- SELECT p.period_id, p.period_start, p.period_end, a.accnt_id, a.accnt_number, a.accnt_descrip, p.period_yearperiod_id, a.accnt_type, SUM(CASE WHEN g.gltrans_date p.period_start THEN g.gltrans_amount ELSE 0.0 END)::text::money AS beginbalance, SUM(CASE WHEN g.gltrans_date = p.period_end AND g.gltrans_date = p.period_start AND g.gltrans_amount = 0::numeric THEN g.gltrans_amount ELSE 0.0 END)::text::money AS negative, SUM(CASE WHEN g.gltrans_date = p.period_end AND g.gltrans_date = p.period_start AND g.gltrans_amount = 0::numeric THEN g.gltrans_amount ELSE 0.0 END)::text::money AS positive, SUM(CASE WHEN g.gltrans_date = p.period_end AND g.gltrans_date = p.period_start THEN g.gltrans_amount ELSE 0.0 END)::text::money AS difference, SUM(CASE WHEN g.gltrans_date = p.period_end THEN g.gltrans_amount ELSE 0.0 END)::text::money AS endbalance FROM period p CROSS JOIN accnt a LEFT JOIN gltrans g ON (g.gltrans_accnt_id = a.accnt_id AND g.gltrans_posted = true) where p.period_id = 58 group by p.period_id, p.period_start, p.period_end, a.accnt_id, a.accnt_number, a.accnt_descrip, p.period_yearperiod_id, a.accnt_type ORDER BY p.period_id, a.accnt_number; ---End Select The query that calls this View -- Select sum( beginBalance ) as beginbalance, sum( negative ) as debit, sum( positive ) as credit, sum( difference ) as difference, sum( endbalance) as endbalance from trailbalance -
Re: [HACKERS] math error or rounding problem Money type
Justin [EMAIL PROTECTED] writes: I believe i have found a math bug/rounding problem with Money type when its used with SUM()... Postgresql 8.3.1 You do know that money only stores two fractional digits? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] math error or rounding problem Money type
Tom Lane wrote: Justin [EMAIL PROTECTED] writes: I believe i have found a math bug/rounding problem with Money type when its used with SUM()... Postgresql 8.3.1 You do know that money only stores two fractional digits? regards, tom lane yes. The question is why are the to sides not equal anymore Take this Select '2', round(sum( beginBalance ),6) as beginbalance, round(sum( negative ),6) as debit, round(sum( positive ),6) as credit, round(sum( difference ),6) as difference, round(sum( endbalance),6) as endbalance from trailbalance union Select '1', sum( round(beginBalance,6)) as beginbalance, sum( round(negative,6)) as debit, sum( round(positive,6)) as credit, sum( round(difference,6)) as difference, sum( round(endbalance,6)) as endbalance from trailbalance 1 -0.06 -11250546.74375211250546.743752 0.00 -0.07 2 0.00-11250546.743752 11250546.743752 0.000.00 At the very least this show a clear warning when rounding do it after all the sum function is called not before.
Re: [HACKERS] math error or rounding problem Money type
Justin wrote: yes. The question is why are the to sides not equal anymore Take this Select '2', round(sum( beginBalance ),6) as beginbalance, round(sum( negative ),6) as debit, round(sum( positive ),6) as credit, round(sum( difference ),6) as difference, round(sum( endbalance),6) as endbalance from trailbalance union Select '1', sum( round(beginBalance,6)) as beginbalance, sum( round(negative,6)) as debit, sum( round(positive,6)) as credit, sum( round(difference,6)) as difference, sum( round(endbalance,6)) as endbalance from trailbalance 1 -0.06 -11250546.74375211250546.743752 0.00 -0.07 2 0.00-11250546.743752 11250546.743752 0.00 0.00 At the very least this show a clear warning when rounding do it after all the sum function is called not before. IFAIK (dimly recalling numerical analysis courses at university) SUM and ROUND can *never* be commuted. In general the recommended approach is to round as late as possible and as few times are possible - so your 1st query is the correct or best way to go. Cheers Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCHES] [HACKERS] TRUNCATE TABLE with IDENTITY
Tom Lane wrote: 2. I had first dismissed Neil's idea of transactional sequence updates as impossible, but on second look it could be done. Suppose RESTART IDENTITY does this for each sequence; * obtain AccessExclusiveLock; * assign a new relfilenode; * insert a sequence row with all parameters copied except last_value copies start_value; * hold AccessExclusiveLock till commit. Hmm, this kills the idea of moving sequence data to a single non-transactional catalog :-( So what I think we should do is leave the patch there, revise the warning per Neil's complaint, and add a TODO item to reimplement RESTART IDENTITY transactionally. I think the TODO item did not make it, but the docs do seem updated. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO, FAQs to Wiki?
Actually, now that I try it, it seems that the MediaWiki markup is not completely helpful here -- right now, on some items we have a one-line header and then possibly a longer description, and it seems the only way to do that in MediaWiki is like this: * Set proper permissions on non-system schemas during db creationbr Currently all schemas are owned by the super-user because they are copied from the template1 database. However, since all objects are inherited from the template database, it is not clear that setting schemas to the db owner is correct. Note the dumb br thing in the middle. Personally I find that ugly enough as to be unacceptable; what do others think? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
