Re: [GENERAL] Query Question (one group by vs two separate)

[Joris Dobbelsteen]

Jonathon Suggs wrote, On 10-12-08 20:12:
I'm asking this as a more general question on which will perform 
better.  I'm trying to get a set of comments and their score/rankings 
from two tables.


*comments*
cid (integer, primary key)
title
body

*comment_ratings*
cid (integer, primary key)
uid (integer, primary key)
score

*Option 1* (Single group by query)
select c.cid,c.title,c.body,coalsece(sum(r.score),0) as score
from comments c left outer join comment_ratings r on c.cid = r.cid
group by c.cid,c.title,c.body;

*Option 2* (Two simple queries)
select cid,title,body from comments;
select cid,sum(score) from comment_ratings;

I know that in general you always want to keep the number of queries to 
a minimum and that Option 1 is the most logical/preferred way to 
proceed.  However, I've greatly simplified Option 1.  In practice I'm 
going to be pulling a lot more columns and will be inner joining to 
additional tables.  /So my real question is whether having a lot of 
items in the group by clause will have an adverse effect on performance./


You can answer that yourself... Create the database and fill it with a 
decent set of sample data (if that doesn't already exists). Run analyze 
to get your statistics. Use explain/explain analyze on the queries you 
are interested in.


The manual has very good references. If you like, pgadmin can even 
create graphical representation (which is what I prefer).


The obvious advantage depends on what you want to achieve? If its just a 
dump of a table and an aggregate, you second option might be better.
If you need both data to transform it into something the user will see, 
probably the first option is better, as you leave combining to the 
highly specialized and optimized software and don't have to do it 
yourself. The choice has to do with index scans and table scans, that 
depend on the situation.


In fact, if the first one doesn't come out nice (first join than 
aggregate, do aggeration in a subquery). So: learn explain!!! Every 
(decent) RDBMS has such a facility.


- Joris

--
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] Are there plans to add data compression feature to postgresql?

[Joris Dobbelsteen]

Gregory Stark wrote, On 01-11-08 14:02:

Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:


But sorry I still can't get WHY compression as a whole and data
integrity are mutually exclusive.

...

[snip performance theory]


Postgres *guarantees* that as long as everything else works correctly it
doesn't lose data. Not that it minimizes the chances of losing data. It is
interesting to discuss hardening against unforeseen circumstances as well but
it's of secondary importance to first of all guaranteeing 100% that there is
no data loss in the expected scenarios.

That means Postgres has to guarantee 100% that if the power is lost mid-write
that it can recover all the data correctly. It does this by fsyncing logs of
some changes and depending on filesystems and drives behaving in certain ways
for others -- namely that a partially completed write will leave each byte
with either the new or old value. Compressed filesystems might break that
assumption making Postgres's guarantee void.


The guarentee YOU want from the underlaying file system is that, in case 
of, lets say, a power failure:


* Already existing data is not modified.
* Overwritten data might be corrupted, but its either old or new data.
* If an fsync completes, all written data IS commited to disk

If an (file) system CAN guarantee that, in any way possible, it is safe 
to use with PostGreSQL (considering my list is complete, of course).


As a side note: I consider the second assumption a bit too strong, but 
there are probably good reasons to do so.



I don't know how these hypothetical compressed filesystems are implemented so
I can't say whether they work or not. When I first wrote the comment I was
picturing a traditional filesystem with each block stored compressed. That
can't guarantee anything like this. 


Instead the discussion reverts to discussing file systems without having 
even a glance at their method of operation. No algorithm used by the 
file system is written down, but these are being discussed.



However later in the discussion I mentioned that ZFS with an 8k block size
could actually get this right since it never overwrites existing data, it
always writes to a new location and then changes metadata pointers. I expect
ext3 with data=journal might also be ok. These both have to make performance
sacrifices to get there though.


Instead, here we are going to specifics we needed a long time ago: ZFS 
takes 8kB as their optimal point(*), and never overwrite existing data. 
So it should be as safe as any other file system, if he is indeed correct.


Now, does a different block size (of ZFS or PostGreSQL) make any 
difference to that? No, it still guarentees the list above.


Performance is a discussion better left alone, since it is really really 
dependent on your workload, installation and more specifics. It could be 
better and it can be worse.


- Joris


(*) Larger block sizes improve compression ratio. However, you pay a 
bigger penalty on writes, as more must be read, processed and written.


--
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] Slow query performance

[Joris Dobbelsteen]

Kevin Galligan wrote, On 29-10-08 23:35:

An example of a slow query is...

select count(*) from bigdatatable where age between 22 and 40 and state 
= 'NY';


explain analyze returned the following...

 Aggregate  (cost=5179639.55..5179639.56 rows=1 width=0) (actual 
time=389529.895..389529.897 rows=1 loops=1)
   ->  Bitmap Heap Scan on bigdatatable  (cost=285410.65..5172649.63 
rows=2795968 width=0) (actual time=6727.848..387159.175 
rows=2553273 loops=1)

 Recheck Cond: ((state)::text = 'NY'::text)
 Filter: ((age >= 22) AND (age <= 40))
 ->  Bitmap Index Scan on idx_jstate  (cost=0.00..284711.66 
rows=15425370 width=0) (actual time=6298.950..6298.950 
rows=16821828 loops=1)

   Index Cond: ((state)::text = 'NY'::text)
 Total runtime: 389544.088 ms

It looks like the index scans are around 6 seconds or so each, but then 
the bitmap heap scan and aggregate jump up to 6 mintues.


Indeed. Its cause is that PostGreSQL must traverse the data in order to 
verify if the data is valid for the transaction. This means A LOT of 
data must be retrieved from disk.


The only real thing you can do is reduce I/O load, by reducing the 
amount of data that must be traversed (or ensuring the data is stored 
closely together, but thats really hard to get right). This requires 
optimizing your database design for that single goal.
This will not make it scale any better than it currently does, however. 
The query will scale O(N) with the size of your table, you want other 
techniques to do better.


Another thing is spending extra money on hardware that can sustain 
higher I/O seek rates (more and/or faster spindles).


- Joris


More detail on the table design and other stuff in a bit...


On Wed, Oct 29, 2008 at 6:18 PM, Scott Marlowe <[EMAIL PROTECTED] 
> wrote:


On Wed, Oct 29, 2008 at 2:18 PM, Kevin Galligan <[EMAIL PROTECTED]
> wrote:
 > I'm approaching the end of my rope here.  I have a large database.
 > 250 million rows (ish).  Each row has potentially about 500 pieces of
 > data, although most of the columns are sparsely populated.

A couple of notes here.  PostgreSQL stores null values as a single bit
in a bit field, making sparsely populated tables quite efficient as
long as you store the non-existent values as null and not '' or some
other real value.

Have you run explain analyze on your queries yet?  Pick a slow one,
run explain analyze on it and post it and we'll see what we can do.



--
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] Are there plans to add data compression feature to postgresql?

[Joris Dobbelsteen]

Grzegorz Jaśkiewicz wrote, On 30-10-08 12:13:


it should, every book on encryption says, that if you compress your data 
before encryption - its better.


Those books also should mention that you should leave this subject to 
experts and have numerous examples on systems that follow the book, but 
are still broken. There are other techniques as well that make breaking 
it harder, such as the CBC and CTS modes.


Using compression consumes processing power and resources, easing DoS 
attacks a lot.


Also I still have to see an compression algorithm that can sustain over 
(or even anything close to, for that matter) 100MB/s on todays COTS 
hardware. As TOAST provides compression, maybe that data can be 
transmitted in compressed manner  (without recompression).


- Joris

--
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] PostgreSQL Cache

[Joris Dobbelsteen]

Matthew Pulis wrote:

Hi,

I need to perform some timed testing, thus need to make sure that disk 
cache does not affect me. Is clearing the OS (Ubuntu) disk cache, ( by 
running:  sudo echo 3 | sudo tee /proc/sys/vm/drop_caches ) enough to 
do this? If not can you please point me to some site please since all 
I am finding is such command.


Look for methodologies for doing performance tests. A problem is that 
the disk cache is an essential part that makes up for postgresql 
performance. Also do not forget about overhead and inaccuracies that you 
will affect your results.


In general performance tests are a rather large simulation of how your 
application would use the database. It should be large enough for many 
effects (such as initial cache state) to be neglected. It only provides 
an average for the performance on your system configuration.
If you run it a few times more, you can compute the variation. It 
provides some insight how stable your system is in handling the workload.


- Joris


--
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] Debian packages for Postgres 8.2

[Joris Dobbelsteen]

Markus Wanner wrote:

Hi,

I'm running several productive servers on Debian etch (stable) with 
Postgres 8.2 which has been in lenny (testing) and made available for 
etch through the backports project [1]. Unfortunately, they 
discontinued maintaining 8.2 and switched to 8.3 in testing and thus 
also for the backports.


As I don't currently want to switch to 8.3 due to the involved 
downtime and upgrading troubles involved. So I've compiled up to date 
Debian packages for Postgres 8.2.10. You can get them (maybe just 
temporarily) from here: http://www.bluegap.ch/debian, I'm providing 
packages as etch-backports for amd64 and i386. Upgrading from earlier 
8.2 backports should work just fine.


I'm trying to convince the backports people to re-add Postgres 8.2. As 
soon as that happens my own repository will probably disappear again.


Please drop me a note if you are interested in 8.2 for etch. (Postgres 
8.3 should become available via the backports within a few days, I 
guess).
I still have interest and I'm actually actively using it. Its a shame, 
as the postgresql community still support 8.2 and probably more rely on it.
Besides this, Debian's tools are well polished and support many versions 
side-by-side.


The good question would be for what reason they have removed the 
backports package? Maybe shortage on maintainers?


- Joris

--
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] Oracle and Postgresql

[Joris Dobbelsteen]

Merlin Moncure wrote:

On Mon, Sep 15, 2008 at 1:10 PM, Martin Gainty <[EMAIL PROTECTED]> wrote:
  

accessing:
i *thought* the advantage of creating any SQL procedure/function was the
entity is stored in procedure cache
load time:
Java vs C++ compare here
http://www.idiom.com/~zilla/Computer/javaCbenchmark.html



This is completely off topic as it is, but I can't help it: anyone who
is arguing that Java is faster than C must be oblivious to the fact
that Java internals are *written in C*.  If Java was really faster
than C, it would be self hosting, and we would be writing operating
systems, databases, and various other systems level stuff in Java.
(yes, there are a few well known projects, notably lucene, but that's
the exception rather than the rule).

Anybody making the case that Java is faster than C simply doesn't know
how a computer works.

merlin
  
According to your argument any hardware implementation is limited to 
software performance, as it is compiled by a software compiler? In fact, 
compiling assembly with a compiler written in C will destroy its 
performance to be worse than C? Come on... This is utter bullshit. You 
should rather reflect on your last line...


Its worst yet, there are actually situation where the Java optimizer 
does a better job than many C compiler. The reverse is also true. If you 
are a software guy you SHOULD know that code optimization and algorithm 
design are NP problems and have no obvious optimal solutions. It seems 
easier to optimize Java code than it is optimizing C code.


Better yet, you should actually read the article. It has the explanation 
contained inside it. The domain of application Java has (eventually) 
been developed for is just different.


Regards,

- Joris

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Efficient processing of staging data

[Joris Dobbelsteen]

Dear,

I'm looking for an efficient way to process data that I have stored in a
staging table. This is syslog output, so it consists of text, where I 
need regexes to filter it out. The data will be split into several/a lot 
of tables.


I currently have a set of queries that
* read from staging and insert into destination table.
* delete from staging (using destination table).
This requires 2 scans over the tables and the index operations (which 
pay of here). I do the processing incrementally, in order to keep run 
time under control. But for only 4 filters this will take around 6 to 30 
minutes.


My later idea was to do a (incremental) table scan on the staging table
and have a function do the processing of the row. The function will then 
either:

* decide to do nothing
* decide to insert the (transformed) row into the destination table and 
delete it from the staging table.


An obvious extension would be to put this processing in the INSERT 
trigger of the staging table, saving the I/O's requires for an insert 
and delete from the staging table. I like to do this afterwards for the 
moment.


What are the recommended methods and helpful implementation hints to get 
it working optimally? I.e. would my method work or are there any better 
solutions possible?


How can I write the filtering functions in such a manner that I can 
later transform the solution in a trigger based one?


Regards,

- Joris Dobbelsteen


--
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] playing with catalog tables limits? dangers? was: seq bug 2073 and time machine

[Joris Dobbelsteen]

Ivan Sergio Borgonovo wrote, On 25-Aug-2008 18:48:

On Mon, 25 Aug 2008 12:07:23 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:


Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:

Alvaro Herrera <[EMAIL PROTECTED]> wrote:

If you're feeling corageous, you can remove the pg_depend
entries for that sequence.  Make sure to try it in a
transaction and drop

I'd like to understand better the risks of being courageous?
I think my life would be easier if I'd know when it is safe to
put hands in the system tables.

Well, it's safe if (a) you know what you're doing, (b) you don't
make any mistakes, and (c) you don't forget any changes needed to
keep all the catalogs consistent.

You can protect yourself against (b) by using a transaction, but
the other two tend to require hacker-grade knowledge of how the
backend works, so we try to discourage people from doing it.


Why hacker-grade knowledge of the backend?
With "hacker-grade" you mean: undocumented or RTSL?


The issue is that 'directly editing the system tables' has NO safety net 
to protect you. You can do everything, even causing assumptions that the 
software will make to become invalid. In general this causes any 
combination of data corruption, server crashes and/or other bad things.


The 'regular interface', that you will find in the documentation, 
ensures that the assumptions remain valid. It will not allow changes 
that cause these assumption to become invalid.
(As a side note: you might see conditions where the assumption are 
violated when using the regular interface: these will be called bugs).


Most of the assumptions are undocumented, but if they happen to be 
documented, there is little reason to actually assume they will remain 
valid over different versions (even between e.g. 8.3 and 8.3.1, though 
that will probably happen, but for other reasons). They are not intended 
to be used by regular users, rather by the system itself.


What Tom calls "hacker-grade" knowledge is that you know what the 
assumptions are and how you can ensure that you will not violate them.
In general, several hackers that work with the system catalogs, probably 
know quite a few of them. Its not something that the DBA should know, 
the commands in the documentation will provide that kind of protection.



Isn't the knowledge about how catalog stuff maps on SQL to "guess"
how to achieve certain results?


This "maps" is dependent on the actual implementation you are running.
(I did have trouble understanding the actual question here).


pg_depend in particular tends to have rather obscure contents,
and what's worse is that messing it up usually doesn't have any
immediately-obvious consequences.


OK... what about concurrent works?
eg. supposing I write the correct SQL should I take care to be the
only one accessing the DB in that moment?


Depends on what you are doing. This is an instance what the regular 
interface enforces. For safety, its probably a good idea to be the only 
one, but its not a requirement.



What could be the use case of directly accessing the catalog?


Ideally, NONE!

However there are cases where strange and unexpected things happen and 
editing the catalog directly can get the database server back into a 
proper/consistent state. You seem to have encountered such a situation.


The main reason for having the option to enable 'direct catalog access' 
is to allow developers to add or modify features and test them while 
they are partially implemented. For example, the feature exists in the 
backend, but no-one implemented a command to actually turn it on or 
modify its parameters. This way the feature can already be tested, while 
they are discussing which commands should be provided to the regular users.



I'd like to have an idea if it is something to invest my time in.
My main interest would be refactoring.


I don't get what you are asking here...

Hope this helps.

Regards,

- Joris

--
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] Referential integrity vulnerability in 8.3.3

[Joris Dobbelsteen]

Richard Huxton wrote, On 15-Jul-2008 15:19:

Sergey Konoplev wrote:
Yes it is. But it the way to break integrity cos rows from table2 
still refer to deleted rows from table1. So it conflicts with

ideology isn't it?


Yes, but I'm not sure you could have a sensible behaviour-modifying 
BEFORE trigger without this loophole. Don't forget, ordinary users can't 
work around this - you need suitable permissions.

>
You could rewrite PG's foreign-key code to check the referencing table 
after the delete is supposed to have taken place, and make sure it has. 
That's going to halve the speed of all your foreign-key checks though.


I did long ago.

For this to work you need to bypass the MVCC rules (to some extend). You 
CANNOT do this with SQL statements, as there is no infrastructure for this.


For now you are bound to native foreign keys or triggers written in C 
using (unsupported?) functions.


- Joris

--
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] Nested IMMUTABLE functions

[Joris Dobbelsteen]

Peter wrote:

I have two immutable Pl/PG funcs - func A takes a parameter X, looks up
related value Y from a table and passes Y to func B. Now, if I do something
like

select A(field_x) from bigtable

it will, of course call A for every single row since paramater is changing.
However, it also calls func B for every row even though most (actually all)
related values Y are the same!

Is this by design, or flaw in optimizer? I thought immutable funcs with the
same arguments are only called once within a scope of single query, and that
'select A(...)' should have counted as single query, right?


No, not really.
Its rather that the optimizer doesn't consider the content of any 
functions that are called. Mostly since this is near to impossible. So 
if you call a function, that function will be executed. Any functions 
calls internally will therefore also be executed.


A second point is that the optimizer CANNOT make any assumptions on your 
data. Your assumption that you look up a value that is nearly always the 
same, is not taken into account by the optimizer.



This stuff is killing me... func B is small, all table lookups optimized to
the hilt but still I'm taking major performance hit as it's called
hundreds/thousands of times.


What you can try is the following:
SELECT B(lookuptable.value)
FROM bigtable INNER JOIN lookuptable ON lookuptable.key = 
A(bigtable.whatever)



Any ideas?


A second part is the cost of the actual function. Depending on the costs 
various things might be chosen by the optimizer. This should at least 
pull out the lookup from your functions, so the optimizer will take them 
into consideration.


I can't tell you how SQL stored procedures are handled, but you can be 
pretty sure that any PL/* languages are considered as normal procedure 
calls by the optimizer.


- Joris

--
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] Strange statistics

[Joris Dobbelsteen]

Henrik wrote:

Hi list,

I'm having a table with a lots of file names in it. (Aprox 3 million) in 
a 8.3.1 db.


Doing this simple query shows that the statistics is way of but I can 
get them right even when I raise the statistics to 1000.


db=# alter table tbl_file alter file_name set statistics 1000;
ALTER TABLE
db=# analyze tbl_file;
ANALYZE
db=# explain analyze select * from tbl_file where lower(file_name) like 
lower('to%');

 QUERY PLAN
 

 Bitmap Heap Scan on tbl_file  (cost=23.18..2325.13 rows=625 width=134) 
(actual time=7.938..82.386 rows=17553 loops=1)

   Filter: (lower((file_name)::text) ~~ 'to%'::text)
   ->  Bitmap Index Scan on tbl_file_idx  (cost=0.00..23.02 rows=625 
width=0) (actual time=6.408..6.408 rows=17553 loops=1)
 Index Cond: ((lower((file_name)::text) ~>=~ 'to'::text) AND 
(lower((file_name)::text) ~<~ 'tp'::text))

 Total runtime: 86.230 ms
(5 rows)


How can it be off by a magnitude of 28??


These are statistics and represent an only estimate! In this case, the 
planner seems to be doing the right thing(tm) anyway.


Statistics is a frequently misunderstood subject and usually provides 
excellent material to draw plain wrong conclusions. There is a good 
chance that due to the physical layout of your data, the algorithms in 
the statistics collector, the existence of uncertainty and some more 
unknown factors your statistics will be biased. This is a situations 
where you noticed it.


Running "SELECT * FROM pg_stats;" will give you the statistics the 
planner uses and can provide some hints to why the planner has chosen 
these estimates.
Probably statistics will vary between ANALYZE runs. Its also possible to 
try "CLUSTER" and friends. Try different queries and look at the deviations.


All in all, you should really start worrying when the planner starts 
planning inefficient queries. Since its a filename, it might be highly 
irregular (random) and a low statistics target might be good enough anyways.


Unfortunately I'm not a statistics expert...

- Joris

--
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] table as log (multiple writers and readers)

[Joris Dobbelsteen]

Gurjeet Singh wrote:
On Wed, Apr 23, 2008 at 12:29 AM, David Wilson <[EMAIL PROTECTED] 
<mailto:[EMAIL PROTECTED]>> wrote:


On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen
<[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
wrote:
 >
 >  Ah, yes, all visible rows...
 >  My point is that, unless you use a transaction with serializable
isolation,
 > this all visible rows for the second statement might be different
from those
 > that you copied into the log table.
 >
 >  With the normal Read committed isolation level you suffer from a
possible
 > nonrepeatable read that might change tuple visibility between
different
 > statements.

That depends on implementation. A select into ... to do the initial
copy followed by a delete where... with the where clause referencing
the log table itself to ensure that we delete only things that now
exist in the log table, or a row by row  insert/delete pair. Either
would provide the appropriate level of protection from accidental
deletion of more things than you intended without harming concurrency.
The delete referencing the log table might require that the log table
be indexed for performance, but it's likely that such indexing would
be done anyway for general log use.


Of course, point is, that is another way to define "visibility" in this 
context: if present in log table. Point is, a suitable definition is needed.


I think this plpgsql function would solve the problem of atomic 
read-and-delete operation...


create or replace function log_rotate() returns void as $$
declare
  rec record;
begin

for rec in delete from t1 returning * loop
insert into t2 values( rec.a, rec.b );
end loop;

end;
$$ language 'plpgsql';

select log_rotate();


Don't forget ordering, this was important before...

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT ... INTO log FROM staging ORDER BY ...;
DELETE FROM staging;
COMMIT;

Don't know if that ORDER BY works. It should in this case.

- Joris

--
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] table as log (multiple writers and readers)

[Joris Dobbelsteen]

David Wilson wrote:

On Tue, Apr 22, 2008 at 9:52 AM, Joris Dobbelsteen
<[EMAIL PROTECTED]> wrote:

 Describe the mechanism, because I don't really believe it yet. I think you
need to do a advisory lock around every commit of every transaction that
writes to the log table.


Consider some number of reader processes and some number of writer processes.

Writer processes touch only the staging table, and solely do inserts
into it. As a result, writer processes cannot interfere with each
other in any way and do not require any synchronization beyond that
provided by MVCC.

Reader processes are interested in polling the logging table at
intervals. In the process, they also act as staging-to-log movers.
This act (because it is destructive and because we require serialized
inserts for id generation in the log table) must take a lock that
prevents other readers from attempting the same work at the same time.

Each reader process therefore has a loop that appears as follows:
1) Obtain advisory lock.
2) Begin transaction.
3) For each row in staging table, insert copy into log table.
4) Delete all visible rows from staging table.


Ah, yes, all visible rows...
My point is that, unless you use a transaction with serializable 
isolation, this all visible rows for the second statement might be 
different from those that you copied into the log table.


With the normal Read committed isolation level you suffer from a 
possible nonrepeatable read that might change tuple visibility between 
different statements.



5) Commit transaction.
6) Release advisory lock.
7) Handle not-yet-seen rows in the logging table (This is the primary
work of the readers)
8) Sleep for the desired interval and return to 1).

We require two types of synchronization and the above takes care of both:
1) The advisory lock prevents multiple readers from doing simultaneous
staging-to-log moves.
2) The transaction block ensures that the reader will see a consistent
state on the staging table while writers may write at the same time;
writes that occur during the reader's transaction block will simply be
ignored during this round of reading.


See above, you demand its impossible for nonrepeatable reads to occur.


You need both types of synchronization to avoid problems- taking an
exclusive lock would simply be the sledgehammer method of doing the
synchronization, since it would take the place of both the advisory
lock and the transaction at the same time but would also block
writers.


I agree with you on this, but it does guarentee the impossibility of a 
nonrepeatable read at the cost of concurrency. There seems to be a 
better solution indeed.


- Joris


--
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] table as log (multiple writers and readers)

[Joris Dobbelsteen]

David Wilson wrote:

On Mon, Apr 21, 2008 at 7:55 PM, Joris Dobbelsteen
<[EMAIL PROTECTED]> wrote:

 If you want to clean up the the staging table I have some concerns about
the advisory lock. I think you mean exclusive table lock.


Either works, really. An advisory lock is really just a lock over
which you have control of the meaning, as long as you're using it in
the appropriate places. Also, an advisory lock on just the processes
doing staging-to-log moves would allow writes into the staging table
to continue concurrently with the staging-to-log transaction (whereas
an exclusive lock would unnecessarily prevent them).


Describe the mechanism, because I don't really believe it yet. I think 
you need to do a advisory lock around every commit of every transaction 
that writes to the log table.


If you are only using the advisory lock in the staging-to-log 
transaction, how would this prevent newly committed tuples to not show 
up during this process? (You can't both delete and insert in a single 
statement, I believe, in which case you won't have a problem anyways).



Also, while Vance appears to have chosen to have a dedicated
staging-to-log process, even that isn't necessary- each reader can
simply do the lock/clear staging/unlock before any attempt to read-
unless you're polling that log table at truly crazy rates, the
overhead should be negligible and will ensure that the staging table
is simply cleared out "whenever necessary" while removing the
complexity of a separate process.


Using serialization mode for the staging-to-log process seems to be the 
most efficient methods, as it won't even block writers.


- Joris

--
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] table as log (multiple writers and readers)

[Joris Dobbelsteen]

David Wilson wrote:

(I originally missed replying to all here; sorry about the duplicate,
Vance, but figured others might be interested.

On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <[EMAIL PROTECTED]> wrote:

 Another approach would be to queue the log entries in a "staging" table,
 so that a single process could move them into the log.  This is fairly
 heavyweight, but it would guarantee the consistent sequencing of the log
 as seen by a reader (even if the order of entries in the log didn't
 always reflect the true commit sequence in the staging table).  I'm
 hoping someone knows a cleverer trick.



Consider a loop like the following

advisory lock staging table
if (entries in table)
   copy entries to main log table as a single transaction
release advisory lock on staging table
read out and handle most recent log entries from main table

The advisory lock is automatically released on client disconnect, and
doing the whole thing within one transaction should prevent any
partial-copies on failures.

It doesn't matter that there are concurrent inserts to the staging
table because the staging table is always wiped all at once and
transferred in a synchronous fashion to the main table. You also can't
lose data, because it's always in one of the two tables.


If you want to clean up the the staging table I have some concerns about 
the advisory lock. I think you mean exclusive table lock.


There are other two options as well:

* Track which data is copies and remove those from the staging table 
that are in the new table.


* Use a serializable mode for the staging-to-log-copying transactions.
In this way you can just copy the table and trow away everything 
(without checking). This seems rather cheap and allows for concurrent 
processing.


- Joris

--
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] table as log (multiple writers and readers)

[Joris Dobbelsteen]

Craig Ringer wrote:
[snip]
If you really want to make somebody cry, I guess you could do it with 
dblink - connect back to your own database from dblink and use a short 
transaction to commit a log record, using table-based (rather than 
sequence) ID generation to ensure that records were inserted in ID 
order. That'd restrict the "critical section" in which your various 
transactions were unable to run concurrently to a much shorter period, 
but would result in a log message being saved even if the transaction 
later aborted. It'd also be eye-bleedingly horrible, to the point where 
even the "send a message from a C function" approach would be nicer.


This will not work for the problem the TS has. Let a single transaction 
hang for a long enough time before commit, while others succeed. It will 
keep ordering of changes, but commits might come unordered.


The issue is, you don't really have the critical section as you 
describe, there is no SINGLE lock you are 'fighting' for.


It will work with an added table write lock (or up), that will be the 
lock for your critical section.


In my opinion I would just forget about this one rather quickly as you 
more or less proposed...


- Joris

--
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] table as log (multiple writers and readers)

[Joris Dobbelsteen]

Andrew Sullivan wrote:

On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote:

It won't work with multiple concurrent writers. There is no guarantee
that an INSERT with a timestamp older than the one you just saw isn't
waiting to commit.


This is pretty unlikely -- I won't say impossible, because I'm sure there's
some kernel-level race condition -- if you use the clock time approach and
SERIALIZABLE mode.


Don't, understand what SERIALIZABLE mode means (in this context):
* It makes restrictions on what you CAN & MAY read (i.e. controls 
visiblity).


If you want to serialize your accesses (which is something completely 
different) you are probably better of with using an exclusive table lock 
on the log table. Otherwise guarantees cannot be made on postgresql. 
Even worse, you MUST NOT use SERIALIZABLE mode if you are going to check 
you log table.


Similarly, handing referential integrity is a complex to handle and has 
special mechanisms in postgresql to handle that.



Going into detail about concurrently running code:
* Without synchronization mechanisms you CANNOT control in which order 
code is executed.



Also, you are not dealing with a race condition here. Its the semantics 
or requirements on the ordering. Race conditions are failures caused by 
a particular ordering (in time) of events, in such a way that corrupts 
the state. There can be prevented by proper locking, but have been 
overlooked (which is easy) and timing constraints are usually very 
stringent so that they are very hard to detect. Therefore, they are 
also, usually, very hard to encounter in common situations.




You could add a trigger that checks for other timestamps
< yours, I suppose.  Of course, that's pretty heavyweight, too.  How much is
the absolute serialization worth to you in performance?


You cannot do this in a reliable way. The Postgresql MVCC semantics are 
such that you can not do that in a reliable manner. The only way is if 
you take at least a write lock on the log table and ensure you are NOT 
in serializable mode.


Remember that you CANNOT view tuples that are not yet committed. In 
fact, with serializable mode you will only see changes from transactions 
that are completed BEFORE you started yours.



The only other thing I can suggest is what someone else did: commit them
with wallclock timestamps, and then have a different thread wake up every
_n_ seconds and put the records into the proper table in timestamp order.


You can guarantee:
* the proper ordering of log events for a single transaction (by 
property of transactions).
* modifications/delete to a single tuple (due to the synchronization 
point caused by the write lock).

This seems enough in the requested case.

You can even do so with a simple sequence counter if you are only 
interested the of ordering in time of events.


- Joris

--
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] database 1.2G, pg_dump 73M?!

[Joris Dobbelsteen]

>-Original Message-
>From: Ross Boylan [mailto:[EMAIL PROTECTED] 
>Sent: Monday, 31 March 2008 0:23
>To: Joris Dobbelsteen
>Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
>Subject: RE: [GENERAL] database 1.2G, pg_dump 73M?!
>
>
>On Sun, 2008-03-30 at 22:59 +0100, Joris Dobbelsteen wrote:
>> >-Original Message-
>> >From: Ross Boylan [mailto:[EMAIL PROTECTED]
>> >Sent: Sunday, 30 March 2008 23:43
>> >To: Joris Dobbelsteen
>> >Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
>> >Subject: Re: [GENERAL] database 1.2G, pg_dump 73M?!
>> >
>> >On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote:
>> >>  From the top contenders, about half are indexes, so you 
>are stuck 
>> >> with ~200 MB of data in the tables.
>> >> Postgresql has some wasted space due to placement of the 
>tuples in 
>> >> a block and overhead for each block and row. I don't know
>> >those values,
>> >> but they are in the range of 24 bytes per tuple, I believe. 
>> >Secondly a
>> >> block is 8 KB by default and tuples cannot be stored into 
>multiple 
>> >> blocks (thats what toast to work around).
>> >> 
>> >> All in all: Lookup tuple sizes, if they are small than 
>the overhead 
>> >> from postgresql can be a big factor. If you are huge you loose on 
>> >> portions of unoccupied space in blocks. I believe pg_statistics 
>> >> will provide this information.
>> >There is a pg_statistic (no "s")  table, but I don't know 
>how to get 
>> >tuple size from it--the documentation refers to the source code to 
>> >figure out the codes.  Backing up a step, I don't know what a tuple 
>> >is in Postgres, and don't see an entry for it in the index.
>> 
>> It was pg_stats.
>> You get avg_width. It gives this per column.
>> 
>> So probably you want
>> SELECT tablename, SUM(avg_width)
>> FROM pg_stats
>> WHERE schemaname = 'public'
>[Thanks; I didn't know about using schemaname to limit it to 
>interesting tables]
>> GROUP BY tablename [RB added] ORDER BY tablename;
> tablename | sum 
>---+-
> client|  62
> counters  |  25
> file  | 109
> filename  |  18
> fileset   |  53
> job   | 149
> jobmedia  |  52
> media | 226
> mediatype |  16
> path  |  82
> pool  | 179
> status|  29
> storage   |  23
> version   |   4
>
>So, for example, if each tuple has 24 bytes of overhead, the 
>overhead more than doubles the size of the file table (18 
>bytes), which has a big record count.  Am I following correctly?

Yes.
(Note not to pin down on the 24 bytes, it varies between versions. I
think this is close enough however).

>Between the space taken up by indices and the other overhead, 
>the size difference between the sql dump and the db disk 
>useage is starting to seem more reasonable.
>
>The database uses SQL-ASCII encoding, so I'd expect the 
>textual parts (filenames and paths) to take up the same space 
>(just for the basic storage, not counting overhead/indices) in 
>Postgres as on the dump, 1 byte/character.

That's the idea.

>I'm not sure what "If you are huge you loose on portions of 
>unoccupied space in blocks" means.

A tuple has to be in exactly 1 page (or block). It cannot span multiple
pages. Hence if your tuples happens to be a half page size or slightly
larger, than only a single one will fit in a single page. So you waste
half the page. You don't have to worry about this, as your tuples are
significantly smaller than that.

(As a reference only: if a tuple is larger than a blocksize some
attributes will be evicted to the toast table or compressed. So that is
to overcome the limitations of 8000 bytes per tuple.)

[snip]
>> 
>> ># select distinct relname, reltuples, relpages from pg_class where 
>> >relkind='r' and substring(relname, 1, 3) != 'pg_';
>> >
>> > relname | reltuples | relpages 
>> >-+---+--
>> > basefiles   | 0 |0
>> > cdimages| 0 |0
>> > client  | 2 |1
>> > counters| 1 |1
>> > device  | 0 |0
>> > file|650659 |11558
>> > filename|623012 | 3958
>> > fileset |22 |1
>> > job |   3

Re: [GENERAL] database 1.2G, pg_dump 73M?!

[Joris Dobbelsteen]

>-Original Message-
>From: Ross Boylan [mailto:[EMAIL PROTECTED] 
>Sent: Sunday, 30 March 2008 23:43
>To: Joris Dobbelsteen
>Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] database 1.2G, pg_dump 73M?!
>
>On Sun, 2008-03-30 at 21:22 +0200, Joris Dobbelsteen wrote:
>>  From the top contenders, about half are indexes, so you are stuck 
>> with ~200 MB of data in the tables.
>> Postgresql has some wasted space due to placement of the tuples in a 
>> block and overhead for each block and row. I don't know 
>those values, 
>> but they are in the range of 24 bytes per tuple, I believe. 
>Secondly a 
>> block is 8 KB by default and tuples cannot be stored into multiple 
>> blocks (thats what toast to work around).
>> 
>> All in all: Lookup tuple sizes, if they are small than the overhead 
>> from postgresql can be a big factor. If you are huge you loose on 
>> portions of unoccupied space in blocks. I believe pg_statistics will 
>> provide this information.
>There is a pg_statistic (no "s")  table, but I don't know how 
>to get tuple size from it--the documentation refers to the 
>source code to figure out the codes.  Backing up a step, I 
>don't know what a tuple is in Postgres, and don't see an entry 
>for it in the index.

It was pg_stats.
You get avg_width. It gives this per column.

So probably you want
SELECT tablename, SUM(avg_width)
FROM pg_stats
WHERE schemaname = 'public'
GROUP BY tablename;

>Is a tuple just a row?  That's what the docs say for the following
>report:

Yes.
Also where I typed "block" it will refer to "page".

># select distinct relname, reltuples, relpages from pg_class 
>where relkind='r' and substring(relname, 1, 3) != 'pg_';
>
> relname | reltuples | relpages 
>-+---+--
> basefiles   | 0 |0
> cdimages| 0 |0
> client  | 2 |1
> counters| 1 |1
> device  | 0 |0
> file|650659 |11558
> filename|623012 | 3958
> fileset |22 |1
> job |   384 |   10
> jobmedia|   596 |7
> location| 0 |0
> locationlog | 0 |0
> log | 0 |0
> media   |   245 |9
> mediatype   | 2 |1
> path| 67908 |  966
> pool| 5 |1
> sql_features|   439 |6
> sql_implementation_info |12 |1
> sql_languages   | 4 |1
> sql_packages|10 |1
> sql_parts   | 9 |1
> sql_sizing  |23 |1
> sql_sizing_profiles | 0 |0
> status  |19 |1
> storage | 2 |1
> unsavedfiles| 0 |0
> version | 1 |1

Do relpages * 8096 / reltuples.
Default installs have 8 KB pages/blocks by default.

For file it should be ~144 bytes/tuple.
For filename it should be ~51 bytes/tuple.

Probably you will get some signficant differences here.

>> Another factor is representation in the SQL dump might be more 
>> efficient than in the database, but this highly depends on your data 
>> set. For example, a int8 takes 8 bytes in a table, while it takes 
>> between 1 and ~20 in a SQL dump.
>> 
>> How the plain SQL dump becomes this small I cannot explain without 
>> much much more details.
>
>On Tom's point, bacula regularly inserts entries into the 
>tables and then, days to months later, deletes them.  As far 
>as I know, the VACUUM FULLs I just did were the first ever; I 
>did do several of them because I kept getting messages about 
>needing more fsm_pages.
>
>I am still trying to figure out if the database was getting 
>any automatic vacuuming at all.  The Postgres documentation 
>(the database is 8.2, though I'm moving to 8.3 soon) sounds as 
>if it's on automatically, but the Debian-specific 
>documentation suggests I may need to do some additional things 
>to enable it.
>
>Probably the fsm_pages being low also hurt disk useage, since 
>the message accompanying the vacuum said that's what happens 
>if fsm_pages is low.  It was 20k; vacuum said I needed 56k, 
>and I upped it to 80

Re: [GENERAL] database 1.2G, pg_dump 73M?!

[Joris Dobbelsteen]

Ross Boylan wrote:

On Sun, 2008-03-30 at 20:27 +0200, Joris Dobbelsteen wrote:
  

Ross Boylan wrote:


I have a postgres server for which du reports
1188072 /var/lib/postgresql/8.2/main
on  Linux system.
The server has only one real database, which is for bacula.  When I dump
the database, it's 73Mg.

This is immediately after I did a full vacuum and restarted the server.

Also,
bacula=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC
limit 15;
 relname | relpages 
-+--

 file_jpfid_idx  |27122
 file_pathid_idx |17969
 file_jobid_idx  |17948
 file_pkey   |14580
 file_fp_idx |12714
 file|11558
 file_filenameid_idx | 9806
 filename| 3958
 filename_name_idx   | 2510
 filename_pkey   | 1367
 path|  966
 path_name_idx   |  950
 path_pkey   |  151
 pg_attribute_relid_attnam_index |   46
 pg_proc |   45

It seems very strange to me that there is such a difference in size
between the dump and the database: the data store is almost 15 time
larger than the dump.

Is this to be expected (e.g., from the indices taking up disk space)?
Is there anything I can do to reclaim some disk space
  

There are a few factors you need to take into account:

* Data storage in the database is packed into blocks and contains
  header data. Since data needs to be put into blocks there is a
  potential for waisting space. If you are unlucky it can become
  nearly a single row in the worst case.
* You need to vacuum often, to ensure obsolete rows are removed and
  space can be reused.
* Tables are not reduced in size and only grown. I thinks cluster
  and vacuum full will reduce the size of your table.
* Indexes are not in the backup, they are derived from the table
  data on a restore.
  If you remove the indexes you are left with 150~200 MB of data (I
  guessed).
  Doing reindex will rebuild the index and get rid of all the bloat
  it has been collected during use.  _I recommend you try this_, as
  your indexes on the file table look quite huge.


reindexing had a huge effect.  After reindex the top tables (file,
filename and path) I now see
 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 15;
 relname | relpages 
-+--

 file|11558
 filename| 3958
 filename_name_idx   | 2383
 file_jpfid_idx  | 2145
 file_fp_idx | 1787
 file_jobid_idx  | 1427
 file_pathid_idx | 1427
 file_pkey   | 1427
 file_filenameid_idx | 1427
 filename_pkey   | 1367
 path|  966
 path_name_idx   |  871
 path_pkey   |  151
 pg_attribute_relid_attnam_index |   46
 pg_proc |   45
and du now reports 451M.  That still seems a bit large, given the size
of the sql dump, but it's almost 2/3 lower than it was before.

Thanks so much!

I guess I need to figure out how to reindex automatically.
  
Take a threshold, e.g. look which indexes are towards the table size, or 
something. The bloat is mostly causes by continues updates to the 
indexes on every insert, update and delete command. The index needs to 
split pages that might be merged back some time later. Doing frequent 
vacuums might, or might not, prevent this. Even in theory you will see 
that algorithms allow trees to grow quite large up to a certain constant 
factor. This is in order to have a good limit on the amount of work that 
must be done on a operation on the index.

But the most important factor for you will be the following:

* Backups are compressed. Since you store filenames and paths these
  will have a very high amount of regularity and therefore are very
  good targets for compression. This can save a huge amount of data.
  If you take a compression factor of 50%~70% you will reach your 70 MB.


I don't see how this is relevant, since my dump file was plain text
(sql).
  
From the top contenders, about half are indexes, so you are stuck with 
~200 MB of data in the tables.
Postgresql has some wasted space due to placement of the tuples in a 
block and overhead for each block and row. I don't know those values, 
but they are in the range of 24 bytes per tuple, I believe. Secondly a 
block is 8 KB by default and tuples cannot be stored into multiple 
blocks (thats what toast to wor

Re: [GENERAL] database 1.2G, pg_dump 73M?!

[Joris Dobbelsteen]

Ross Boylan wrote:

I have a postgres server for which du reports
1188072 /var/lib/postgresql/8.2/main
on  Linux system.
The server has only one real database, which is for bacula.  When I dump
the database, it's 73Mg.

This is immediately after I did a full vacuum and restarted the server.

Also,
bacula=> SELECT relname, relpages FROM pg_class ORDER BY relpages DESC
limit 15;
 relname | relpages 
-+--

 file_jpfid_idx  |27122
 file_pathid_idx |17969
 file_jobid_idx  |17948
 file_pkey   |14580
 file_fp_idx |12714
 file|11558
 file_filenameid_idx | 9806
 filename| 3958
 filename_name_idx   | 2510
 filename_pkey   | 1367
 path|  966
 path_name_idx   |  950
 path_pkey   |  151
 pg_attribute_relid_attnam_index |   46
 pg_proc |   45

It seems very strange to me that there is such a difference in size
between the dump and the database: the data store is almost 15 time
larger than the dump.

Is this to be expected (e.g., from the indices taking up disk space)?
Is there anything I can do to reclaim some disk space

There are a few factors you need to take into account:

   * Data storage in the database is packed into blocks and contains
 header data. Since data needs to be put into blocks there is a
 potential for waisting space. If you are unlucky it can become
 nearly a single row in the worst case.
   * You need to vacuum often, to ensure obsolete rows are removed and
 space can be reused.
   * Tables are not reduced in size and only grown. I thinks cluster
 and vacuum full will reduce the size of your table.
   * Indexes are not in the backup, they are derived from the table
 data on a restore.
 If you remove the indexes you are left with 150~200 MB of data (I
 guessed).
 Doing reindex will rebuild the index and get rid of all the bloat
 it has been collected during use.  _I recommend you try this_, as
 your indexes on the file table look quite huge.

But the most important factor for you will be the following:

   * Backups are compressed. Since you store filenames and paths these
 will have a very high amount of regularity and therefore are very
 good targets for compression. This can save a huge amount of data.
 If you take a compression factor of 50%~70% you will reach your 70 MB.

Ow, server restarts will not help reduce your database size. In fact, 
nothing at all should change, except lower performance until sufficient 
cached data is back in the cache again.


Hope this helps...

- Joris

--
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] Conditional JOINs ?

[Joris Dobbelsteen]

Leon Mergen wrote:

On 3/19/08, Erik Jones <[EMAIL PROTECTED]> wrote:
  

 >> Excuse me for bumping this up again, but I still don't understand how
 >> to use this approach to sequentially walk through all different child
 >> tables in one select, without having to JOIN these tables all the
 >> time
 >
 > Apparently a UNION all solved this problem -- sorry for the noise.


If you have the child tables INHERITing from the parent, then a simple

 SELECT parent.* FROM parent;

 would be equivalent to manually spelling out a UNION ALL that
 explicitly lists all of the tables.



But this will only display the information that is common for all the
child tables -- if I also want to display all the information that is
specific for the child tables, as I understand it, I have to use a
UNION ALL and merge all the child tables together this way.

The EXPLAIN of this query:

 Append  (cost=0.00..2169.52 rows=34376 width=94)
   ->  Seq Scan ON child1 (cost=0.00..1824.71 rows=34371 width=94)
   ->  Seq Scan ON child2  (cost=0.00..1.05 rows=5 width=56)

Regards,

Leon Mergen

  


What I think you desire is more in the form of:
SELECT id, foo, bar, NULL AS "baz"
FROM child1
UNION ALL
SELECT id, foo, NULL, baz
FROM child2.

I think if you compare it to I/O volume, the joins will not cause many 
additional  I/Os as long as the indexes on "id" for tables child1 and 
child2 will fit into memory.


- Joris

--
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] Feature request/suggestion - CREATE SCHEMA LIKE

[Joris Dobbelsteen]

wstrzalka wrote:

Hi

   Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE
are very usefull but it would be great to have such a feature on the
mid-level too. I mean something CREATE SCHEMA LIKE that would copy all
the template schema relations, etc...
 What do you think about it ? Would it be hard to implement ? Is it
worth the effort 
I believe the CREATE DATABASE was because of the way postgresql creates 
a database. I thought it just copied the template database (but are not 
completely sure). I also believe CREATE TABLE LIKE was mostly for 
temporary tables, where the query can not be sure what the table 
actually looks like when it is invoked.


Would it not be possible to work around the SCHEMA LIKE by just dumping 
the database schema and restoring it to a new schema? This seems more 
like a part that should be under strict user control and not automated 
by common queries.


- Joris

--
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] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: Wednesday, 5 March 2008 0:29
>To: Joris Dobbelsteen
>Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Planner: rows=1 after "similar to" 
>where condition. 
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
[snip]
>> "  Recheck Cond: (((program)::text = 'amavis'::text) AND 
>> ((facility)::text = 'mail'::text))"
>
>selects rows that match the regex with much higher probability 
>than the general row population does.  Since we don't yet have 
>any cross-column statistics the planner has no chance of 
>realizing that.

Yes, that was it, should have realized that.

Thanks,

- Joris

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Joris 
>Dobbelsteen
>Sent: Monday, 25 February 2008 17:08
>To: Tom Lane
>Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Planner: rows=1 after "similar to" 
>where condition. 
>
>>-Original Message-
>>From: Tom Lane [mailto:[EMAIL PROTECTED]
>>Sent: Monday, 25 February 2008 16:34
>>To: Joris Dobbelsteen
>>Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org
>>Subject: Re: [GENERAL] Planner: rows=1 after "similar to" 
>>where condition. 
>>
>>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>>> "Bitmap Heap Scan on log_syslog syslog  (cost=11168.32..16988.84
>>> rows=1
>>> width=221) (actual time=11145.729..30067.606 rows=212 loops=1)"
>>> "  Recheck Cond: (((program)::text = 'amavis'::text) AND 
>>> ((facility)::text = 'mail'::text))"
>>> "  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~
>>> '***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed
>>[A-Za-z0-9]+,
>>> [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>,
>>> (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+,
>>> queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
>>
>>It's not too surprising that you'd get a small selectivity 
>estimate for 
>>such a long regexp; the default estimate is just based on the 
>amount of 
>>fixed text in the pattern, and you've got a lot.
>>
>>If you increase the stats target for the column to 100 or 
>more then it 
>>will try actually applying the regexp to all the histogram entries.
>>That might or might not give you a better estimate.
>
>I will try that, expect result back within a few days (have it 
>collect some better sample set). Unfortunally the regex is not 
>so much for narrowing down the selection, but rather 
>guarenteeing the format of the messages.
>You seem to consider the common case differently, and I can 
>agree for most part. Unfortunally my use-case is different 
>from the expected. That said, might a less aggressive 
>selectivity estimation for long strings work better in the common case?

A new test case (I did a fresh VACUUM ANALYZE with your statistics for
text set to 100):
Arround 5288 rows out of 4.3 Million match.

"Bitmap Heap Scan on log_syslog syslog  (cost=1.94..53522.27 rows=1
width=226) (actual time=41661.354..92719.083 rows=5288 loops=1)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND
((facility)::text = 'mail'::text))"
"  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~
'***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed [A-Za-z0-9]+,
[][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>,
(Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+,
queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
"  ->  BitmapAnd  (cost=1.94..1.94 rows=15279 width=0) (actual
time=4641.009..4641.009 rows=0 loops=1)"
"->  Bitmap Index Scan on "IX_log_syslog_program"
(cost=0.00..2908.86 rows=113370 width=0) (actual time=2913.718..2913.718
rows=113897 loops=1)"
"  Index Cond: ((program)::text = 'amavis'::text)"
"->  Bitmap Index Scan on "IX_log_syslog_facility"
(cost=0.00..14868.57 rows=591426 width=0) (actual
time=1715.591..1715.591 rows=586509 loops=1)"
"  Index Cond: ((facility)::text = 'mail'::text)"
"Total runtime: 92738.389 ms"

Unfortunally, Tom, it seems the data varies to much and is not included
in the histogram. Probably the data varies too much. In this case, a
regex NOT for selection but rather for forcing the input format should
be done differently.
My construction with the regex as "substring()" construction and a
"WHERE substring() IS NOT NULL" seems to give a better estimate in these
cases. The result seems equivalent.

"Bitmap Heap Scan on log_syslog syslog  (cost=17783.78..53966.33
rows=5844 width=226) (actual time=59095.076..110913.152 rows=5295
loops=1)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND
((facility)::text = 'mail'::text))"
"  Filter: (((priority)::text = 'notice'::text) AND
("substring"((text)::text, 'amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\)
Passed \\"[A-Za-z0-9]+\\", [][0-9.]* <[^<>]+> -> <[^<>]+>, Message-ID:
<[^<

Re: [GENERAL] win32: how to backup (dump does not work)

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Gevik 
>Babakhani
>Sent: Tuesday, 26 February 2008 22:30
>To: [EMAIL PROTECTED]; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] win32: how to backup (dump does not work)
>
>> AFAIK stopping the server, zipping data dir, and restarting 
>the server 
>> creates a zip file which is not easily portable to other 
>computers due 
>> to some ntfs file system permission problems.
>> 
>IIRC you have to re-assign owner and change permissions after 
>you copy your data dir to the new server.

I believe something like NTBackup (or any decent windows backup tool)
can properly restore permissions. However, this will only work with
centralized authentication (with NT domain) and NO local computer
accounts used, when moving between servers (for obvious reasons). I
think even WinRAR contains an option to do so, but cannot remember for
sure and I never tried it anyways.

In cause you mean the permissions prevent you from making a backup, this
should be a smaller problem if you are infact a backup operator and use
a windows backup tool (they get more rights when requested).

- Joris


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

Resent due to bounce... 
orange.nl #5.0.0 X-SMTP-Server; host sss.pgh.pa.us[66.207.139.130] said:
550

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: Monday, 25 February 2008 16:34
>To: Joris Dobbelsteen
>Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Planner: rows=1 after "similar to" 
>where condition. 
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>> "Bitmap Heap Scan on log_syslog syslog  (cost=11168.32..16988.84 
>> rows=1
>> width=221) (actual time=11145.729..30067.606 rows=212 loops=1)"
>> "  Recheck Cond: (((program)::text = 'amavis'::text) AND 
>> ((facility)::text = 'mail'::text))"
>> "  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~
>> '***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed 
>[A-Za-z0-9]+,
>> [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>,
>> (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+,
>> queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
>
>It's not too surprising that you'd get a small selectivity 
>estimate for such a long regexp; the default estimate is just 
>based on the amount of fixed text in the pattern, and you've got a lot.
>
>If you increase the stats target for the column to 100 or more 
>then it will try actually applying the regexp to all the 
>histogram entries.
>That might or might not give you a better estimate.

I will try that, expect result back within a few days (have it collect
some better sample set). Unfortunally the regex is not so much for
narrowing down the selection, but rather guarenteeing the format of the
messages.
You seem to consider the common case differently, and I can agree for
most part. Unfortunally my use-case is different from the expected. That
said, might a less aggressive selectivity estimation for long strings
work better in the common case?

Might an alternative be to use a function and check for a positive
result, i.e. something that the predictor cannot take into account?

- Joris



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: Monday, 25 February 2008 16:34
>To: Joris Dobbelsteen
>Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Planner: rows=1 after "similar to" 
>where condition. 
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>> "Bitmap Heap Scan on log_syslog syslog  (cost=11168.32..16988.84 
>> rows=1
>> width=221) (actual time=11145.729..30067.606 rows=212 loops=1)"
>> "  Recheck Cond: (((program)::text = 'amavis'::text) AND 
>> ((facility)::text = 'mail'::text))"
>> "  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~
>> '***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed 
>[A-Za-z0-9]+,
>> [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>,
>> (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+,
>> queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
>
>It's not too surprising that you'd get a small selectivity 
>estimate for such a long regexp; the default estimate is just 
>based on the amount of fixed text in the pattern, and you've got a lot.
>
>If you increase the stats target for the column to 100 or more 
>then it will try actually applying the regexp to all the 
>histogram entries.
>That might or might not give you a better estimate.

I will try that, expect result back within a few days (have it collect
some better sample set). Unfortunally the regex is not so much for
narrowing down the selection, but rather guarenteeing the format of the
messages.
You seem to consider the common case differently, and I can agree for
most part. Unfortunally my use-case is different from the expected. That
said, might a less aggressive selectivity estimation for long strings
work better in the common case?

Might an alternative be to use a function and check for a positive
result, i.e. something that the predictor cannot take into account?

- Joris


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

>-Original Message-
>From: Gregory Stark [mailto:[EMAIL PROTECTED] 
>Sent: Monday, 25 February 2008 12:31
>To: Joris Dobbelsteen
>Cc: Scott Marlowe; pgsql-general@postgresql.org
>Subject: Re: Planner: rows=1 after "similar to" where condition.
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>
>> Should be:
>> PostGreSQL 8.2.5 on x86_64-pc-linux-gnu (GCC 4.1.2.20061115) 
>(Debian 4.1.1-21).
>
>In this case that may matter. One of the changes in the 8.2.6 
>update was:
>
>. Improve planner's handling of LIKE/regex estimation in non-C 
>locales (Tom)
>
>I seem to recall this largely had to do with negated regexp 
>matches but I might only be remembering part of it. I would 
>start by taking all the fixes for known bugs before trying to 
>diagnose a new one :)

It seems debian backports are not upgraded on my system. So I performed
the upgrade to:
"PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)"
And indeed, I should have upgraded it before to the latest version.

It still spits out the same EXPLAIN ANALYZE result.

"Bitmap Heap Scan on log_syslog syslog  (cost=11168.32..16988.84 rows=1
width=221) (actual time=11145.729..30067.606 rows=212 loops=1)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND
((facility)::text = 'mail'::text))"
"  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~
'***:^(?:amavis\\[[0-9]+\\]: \\([0-9]+-[-0-9]+\\) Passed [A-Za-z0-9]+,
[][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>,
(Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+,
queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
"  ->  BitmapAnd  (cost=11168.32..11168.32 rows=2095 width=0) (actual
time=608.771..608.771 rows=0 loops=1)"
"->  Bitmap Index Scan on "IX_log_syslog_program"
(cost=0.00..490.06 rows=19160 width=0) (actual time=94.982..94.982
rows=85238 loops=1)"
"  Index Cond: ((program)::text = 'amavis'::text)"
"->  Bitmap Index Scan on "IX_log_syslog_facility"
(cost=0.00..10677.75 rows=426214 width=0) (actual time=504.960..504.960
rows=455084 loops=1)"
"  Index Cond: ((facility)::text = 'mail'::text)"
"Total runtime: 30068.868 ms"

From what little I know statistics and how, in postgres, the filter
expressions are computed, the "rows=1" prediction seems still a bit odd.

It seems the fix did not effect this query.

Thanks for your support so far,

- Joris


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

>-Original Message-
>From: Scott Marlowe [mailto:[EMAIL PROTECTED] 
>Sent: Monday, 25 February 2008 7:14
>To: Joris Dobbelsteen
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Planner: rows=1 after "similar to" 
>where condition.
>
>On Sun, Feb 24, 2008 at 4:35 PM, Joris Dobbelsteen 
><[EMAIL PROTECTED]> wrote:
>> I seem to have some planner oddity, where it seems to completely  
>> mispredict the output after a regex compare. I've seem it on other  
>> occasions, where it completely screws up the join. You can note the  
>> "rows=1" after the filter.
>>  A similar sitution has occurred when doing a regex filter in a 
>> subquery,  which was subsequently predited as 1 row and triggered 
>> (oddly enough) a  sequencial scan. Doing the same using 
>"equality" on 
>> the result to  substring( from ) seemed to work and 
>> produced a useful  plan, since it did a hash-join (as it 
>should have).
>>  Is this a known problem? Otherwise I think I should build a smaller 
>> test  case...
>>
>>  Using Postgresql 8.2.6 from Debian Etch-backports.

Should be:
PostGreSQL 8.2.5 on x86_64-pc-linux-gnu (GCC 4.1.2.20061115) (Debian
4.1.1-21).
Should have paid closer attention.

>>
>>  "Bitmap Heap Scan on log_syslog syslog  (cost=13124.26..51855.25 
>> rows=1  width=270)"
>>  "  Recheck Cond: (((program)::text = 'amavis'::text) AND  
>> ((facility)::text = 'mail'::text))"
>>  "  Filter: ***SOME VERY LONG SIMILAR TO REGEX"
>>  "  ->  BitmapAnd  (cost=13124.26..13124.26 rows=18957 width=0)"
>>  "->  Bitmap Index Scan on "IX_log_syslog_program"
>>  (cost=0.00..2223.95 rows=92323 width=0)"
>>  "  Index Cond: ((program)::text = 'amavis'::text)"
>>  "->  Bitmap Index Scan on "IX_log_syslog_facility"
>>  (cost=0.00..10899.81 rows=463621 width=0)"
>>  "  Index Cond: ((facility)::text = 'mail'::text)"
>
>It's not saying it will only get one row back for sure, it's 
>saying it thinks it will return one row.  and depending on 
>your query, it might.
> What's the query, and what's the explain analyze of that query?
>

See the attached file for the query and the explain (hopefully this
gives a consistent view and maintains the layout for easier reading).

The point is that it will NOT, not even close. The planner guesses 1
row, but the output was arround 13000 rows (of the 2.2M rows in the
table). Oddly enough the 18k rows on the bitmap and seems a very good
estimation. In fact, if I omit the "SIMILAR TO", it estimates ~12000
rows, which is spot on. So it seems the SIMILAR TO really gets the
planner confused.

The real value was 12981 rows that were returned by the query in the
first case. However, since I removed this data from the original table
(its now somewhere else), I cannot present the original EXPLAIN ANALYZE
any more. The new dataset only contains ~137 (but I still have the old
statistics, I think, or at least they provide the same predictions).
I also included a run after EXPLAIN ANALYZE on the current dataset.

Hopefully this helps.

Thanks,

- Joris


QUERY=



SELECT  a.msgid, a.rcv_time, a.sent_time, a.hostname, 
a.passedas, a.from, a.to,
a.message_id, a.resent_message_id,
a.mail_id,
a.queue_id,
convert_score(a.spamscore) AS spamscore,
CAST(a.duration AS integer)
FROM(SELECT syslog.msgid, syslog.rcv_time, syslog.sent_time, 
syslog.hostname,
substring(syslog.text from 'amavis% Passed #"%#", % <%> -> <%>, 
Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as passedas,
substring(syslog.text from 'amavis% Passed %, % <#"%#"> -> <%>, 
Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as from,
substring(syslog.text from 'amavis% Passed %, % <%> -> <#"%#">, 
Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as to,
substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, 
Message-ID: <#"[^<>]+#">, (Resent-Message-ID: <#"%#">, |)mail_id: %, Hits: %, 
queued_as: %, % ms' for '#') as message_id,
substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, 
Message-ID: <%>, Resent-Message-ID: <#"%#">, mail_id: %, Hits: %, queued_as: %, 
% ms' for '#') as resent_message

[GENERAL] Planner: rows=1 after "similar to" where condition.

[Joris Dobbelsteen]

I seem to have some planner oddity, where it seems to completely
mispredict the output after a regex compare. I've seem it on other
occasions, where it completely screws up the join. You can note the
"rows=1" after the filter.
A similar sitution has occurred when doing a regex filter in a subquery,
which was subsequently predited as 1 row and triggered (oddly enough) a
sequencial scan. Doing the same using "equality" on the result to
substring( from ) seemed to work and produced a useful
plan, since it did a hash-join (as it should have).
Is this a known problem? Otherwise I think I should build a smaller test
case...

Using Postgresql 8.2.6 from Debian Etch-backports.

"Bitmap Heap Scan on log_syslog syslog  (cost=13124.26..51855.25 rows=1
width=270)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND
((facility)::text = 'mail'::text))"
"  Filter: ***SOME VERY LONG SIMILAR TO REGEX"
"  ->  BitmapAnd  (cost=13124.26..13124.26 rows=18957 width=0)"
"->  Bitmap Index Scan on "IX_log_syslog_program"
(cost=0.00..2223.95 rows=92323 width=0)"
"  Index Cond: ((program)::text = 'amavis'::text)"
"->  Bitmap Index Scan on "IX_log_syslog_facility"
(cost=0.00..10899.81 rows=463621 width=0)"
"  Index Cond: ((facility)::text = 'mail'::text)"

- Joris


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] More formal definition of functions in documentation

[Joris Dobbelsteen]

Dear,

I'm currently reading through the Postgresql documentation about how
several functions work and which ones I would need. So far the
documentation is great and well-structured!

Unfortunally I'm not sure what functions will actually do when some
non-obvious input is provided (which is sometimes required, as we cannot
assume structure of the input at all times). A simple example is
substring(text from 'blaat#"%#"' for '#')
where text is not in the format of the regular expression, e.g. when
text = 'text'. I don't know if the SQL standard includes such
requirements, but documenting such behaviour might be a good addition to
the help.
If I know how and how to structure it, I'm willing to do some effect.

In any case, something as already done for CREATE TABLE and such
constructs are very good and well-defined.

- Joris


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] how to add array of objects to a record

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of dfx
>Sent: Sunday, 3 February 2008 10:38
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] how to add array of objects to a record
>
>Hi list,
>
>Can I add an array of object to a record?
>
>For example if I have a class (or type) phone_number:
>
>create type phone_number as(
>name char(20),
>caption char(50),
>ph_num char(25));
>
>and I would like associate several (unknown number, a priori) 
>phone numbers to a record "persons"
>can I create a table like this:
>
>create table persons(
>id integer,
>first_name char(50),
>family_name char(50),
>phone_numbers phone_number[])
>
>and how I have to write the insert and the select queries to 
>put and get the array of phone numbers?

Its an SQL database, do not fall back to paradigms from imperative
programming languages (like C, Java, PHP, Perl, ...). Postgres is an
implementation of the relational model, which works very nice on sets.
It does not have a good concept of pointers and alike (they can be
represented, but inefficiently).

Transform you type into a table and add an "id" attribute that
references the persons table (using a foreign key). At this point you
can use regular SQL statements.

- Joris


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] newbie question

[Joris Dobbelsteen]

>-Original Message-
>From: Russell Aspinwall [mailto:[EMAIL PROTECTED] 
>Sent: woensdag 17 oktober 2007 11:37
>To: Joris Dobbelsteen
>Subject: Re: [GENERAL] newbie question
>
>Joris Dobbelsteen wrote:
>>> -Original Message-
>>> From: [EMAIL PROTECTED]
>>> [mailto:[EMAIL PROTECTED] On Behalf Of Russell 
>>> Aspinwall
>>> Sent: woensdag 17 oktober 2007 9:34
>>> To: pgsql-general@postgresql.org
>>> Subject: [GENERAL] newbie question
>>>
>>> Hi,
>>>
>>> [snip] For example, if you had a
>>> built a database and application using version 3 and then upgraded 
>>> the version 7, it was possible to set the database configuration to 
>>> version 3 and then continue to use the same database and 
>applications 
>>> without having to dump the database tables and data then 
>import them 
>>> into a  version 7 database or update applications.
>>> Does this feature exist in ProgreSQL, can a v8 access a database 
>>> created using v7?
>>> 
>>
>> No it cannot. You must perform a dump and restore.
>>
>> Also note that between different architectures (and 
>sometimes between 
>> different compiles) the file format might also be different.
>> See also:
>> 
>http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html.
>>
>> However all queries running on v7 should work on v8. The application 
>> should not require any modifications. In practice you should, of 
>> course, test that before putting it into production.
>
>Thank you for the reply, could this feature be added in future?
>

That's not a question for me, but rather for the people who spend a lot
of time creating this excellent database server.

Nevertheless, given that it:

* significantly complicates the software
* might hinder (some) new features to be implemented
* there is little demand, hence nobody is willing to spend the
incredible amount of time on programming & maintenance
* Binary format already differs between different processor
architectures/compliations
* the SQL interface itself already shouldn't change (so from the
application point of view)

I would say that such a feature is highly unlikely that it will be added
in the foreseeable future.


A more likely feature is inplace/live (whatever you call it) upgrades of
the data to a newer version.


- Joris



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] newbie question

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Russell Aspinwall
>Sent: woensdag 17 oktober 2007 9:34
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] newbie question
>
>Hi,
>
>[snip] For example, if you had a 
>built a database and application using version 3 and then 
>upgraded the version 7, it was possible to set the database 
>configuration to version 3 and then continue to use the same 
>database and applications without having to dump the database 
>tables and data then import them into a  version 7 database or 
>update applications.
>Does this feature exist in ProgreSQL, can a v8 access a 
>database created using v7?

No it cannot. You must perform a dump and restore.

Also note that between different architectures (and sometimes between
different compiles) the file format might also be different.
See also:
http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html.

However all queries running on v7 should work on v8. The application
should not require any modifications. In practice you should, of course,
test that before putting it into production.

- Joris


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Proposed Feature

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Magnus Hagander
>Sent: zaterdag 23 juni 2007 11:39
>To: Naz Gassiep
>Cc: Tony Caduto; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Proposed Feature
>
>Naz Gassiep wrote:
>> Hey,
>> I'm sure that'd be greatly appreciated, most other major servers 
>> and DBs have a similar feature, and that's what the systray is for, 
>> i.e., viewing major user-installed services.
>
>Don't forget that the tray (or taskbar notification area as 
>it's supposed to be called) is one of the most abused areas of 
>the windows GUI. I've seen installs where it takes up half the 
>screen. So it's not
>*necessarily* a good thing - making it mandatory to have an 
>easy way to turn it off. And if we don't add any actual 
>*functionality*, it should absolutely not be enabled by 
>default. Providing it as an option is never wrong, though.

I fully agree with the not part of the default installation. And make it
easy to turn the thing off.

In that respect it sound like a good feature for developer systems (not
servers per se). Just ensure for a small memory footprint, preferably
within a few 100 KB of memory. If you are going over 1 MB you are
seriously doing something wrong. (Obviously that might be acceptable
when I'm actively using it, but otherwise it should keep memory usage as
low as resonably possible).

Secondly it might be a good feature to let the user choose at
installation time to install postgres as a service and either start it
on boot (server) or have the user control when it should start
(developer system). In the latter case, such a tray icon might be a very
good feature (since it keeps my 1.5 GB of memory less occupied than
full, though postgresql is already quite a friendly program in this
respect).

- Joris


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] pointer to feature comparisons, please

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Kevin Hunter
>Sent: woensdag 13 juni 2007 22:03
>To: Stefan Kaltenbrunner
>Cc: PostgreSQL General List
>Subject: Re: [GENERAL] pointer to feature comparisons, please
>
>At 3:26p -0400 on 13 Jun 2007, Stefan Kaltenbrunner wrote:
>>> The way that I currently know how to do this in Postgres is with 
>>> PLpgSQL functions.  Then I add something like
>>>
>>> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying ( 
>>> awayteamid, timeid ) )
>>>
>>> to the table schema.
>>
>> well doing it that way is usually not a good idea at all (you cannot 
>> actually use arbitrary queries in a CHECK constraint in pg either - 
>> using a function to hide that is cheating the database - 
>oracle might 
>> actually be more(!) clever here not less ...). this why you can get 
>> into all kind of weird situations with losing the integrity of your 
>> data or running into serious issues during dump/ restore for example.
>
>I was /hoping/ for a response like this!  Thanks!  Okay.  I'll bite.   
>Why can't they be used in general?  Is it the same problem 
>that the trigger has (below)?
>
>> What you need to do here is to use a trigger.
>
> From online docs regarding Oracle, this is not 100% safe either:
>
>(http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10795/
>adfns_co.htm)
>'To enforce this rule without integrity constraints, you can 
>use a trigger to query the department table and test that each 
>new employee's department is valid. But this method is less 
>reliable than the integrity constraint. SELECT in Oracle 
>Database uses "consistent read", so the query might miss 
>uncommitted changes from other transactions.'

For constraints, you don't want that to happen obviously...
In fact, if you run serializable the problems are even bigger.

In Oracle you should use SELECT FOR UPDATE for such constraints. They do
interfere with concurrency a bit, but you can in fact guarentee you
constraints (to a certain better point). It does require a lot of
thought nevertheless and its troublesome to get right.

In PostGreSQL there are more limitations to guarenteeing such
constraint. You can go a long with with SELECT FOR SHARE, but you can
run into problems when using serializable isolation. It's a bit better
on concurrency (it seems), but cannot enforce the constraint up to the
level Oracle can.

It's a tricky subject, it requires a lot of work for a single
constraint. Also you must be very aware of the limitations of such
constructs, since many are impossible to guarentee at this point in
time. In general, the world is less concerned with it.

>It seems to me that there are certain situations where, 
>especially in a highly normalized data model, that you'd 
>/have/ to have multiple checks of even other tables.  What 
>theory am I missing if this is not the case?
>
>(I'm curious as well for another project on which I'm working 
>that does use pg and currently uses a function in just this fashion.)

They should use triggers. Also sometimes it possible to transform the
database schema in a way that you can enforce the constraint with
build-in (foreign key) constraints.

The general problem with these type of constraints is that they are
assumed to be true at ALL times. However it is possible to violate the
constraint, contradicting the assumption we just made. For triggers
there do not exist such assumptions.
Unless the database is going to support constraints with subqueries
(which is very hard to achieve and quite involved), we cannot rely on
the assuption that constraints are always true. In addition, don't
expect this type of support anytime soon on any opensource/commercial
database.

- Joris


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: Creditcard Number Security was Re: [GENERAL] Encrypted column

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Marko Kreen
>Sent: dinsdag 5 juni 2007 21:38
>To: Peter Childs
>Cc: pgsql-general@postgresql.org
>Subject: Re: Creditcard Number Security was Re: [GENERAL] 
>Encrypted column
>
>On 6/5/07, Peter Childs <[EMAIL PROTECTED]> wrote:
>> On 05/06/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote:
>> > On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote:
>> > >
>> > > If he is a CC customer, the system (which I am DBA of) bills his 
>> > > card directly, saving the customer much time and effort.
>> >
>> > So surely what you have is a completely separate system that has 
>> > exactly one interface to it, that is signaled to provide a 
>> > transaction number and that only ever returns such a transaction 
>> > number to the "online" system, and that is very tightly secured, 
>> > right?
>> >
>> > It is possible to make trade-offs in an intelligent manner, for 
>> > sure, but you sure as heck don't want that kind of data stored 
>> > online with simple reversible encryption.
>>
>>  Unfortunately you still need to store them somewhere,  and all 
>> systems can be hacked.  Yes its a good idea to store them on a 
>> separate system and this is an important part of designing your 
>> systems to ensure that the simple user interface is somehow limited.
>
>If you really need the number in cleartext you should use 
>public-key encryption, either via pgcrypto or in application.
>
>Thus you can have only public-key in public database, 
>credit-card numbers are encrypted with it, later actual 
>billing happens in separate, highly secured system that has 
>corresponding private key available to decrypt the data.

Even better is to have security experts/specialists design and formally
validate the system before use. In general people will screw up security
in so much ways that it easilly goes beyond your imagination.

You can also take established systems, like kerberos. The problem here
is the integration of different data systems. But generally these
systems (not all) are well-designed and have received attention from
specialists, giving you a much higher confidence in their secure
operation than something you build yourselfs.
Of course, this still doesn't mean the entire system you are buidling is
secure.


For elaboration only:

Obviously for credit cards we are looking only at the database. Did
anyone realize where the credit cards numbers happened to pass through?
First of, at the user side they are entered into the browser. Then they
are (securely) transmitted to the web server, which already can see
them. Eventually they end up in a database and get send to a bank.

Obviously putting the information using public key encyrption in a
database isn't going to help you securing your web server, is it? So
though considering a small part of the system, many important aspects
are already overlooked. Yet the weakest chain determines the strength of
the entire system.


Leave security to specialist, it's a really really hard to get right.


- Joris Dobbelsteen


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Delete with subquery deleting all records

[Joris Dobbelsteen]

>-Original Message-
>From: Francisco Reyes [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 24 mei 2007 2:04
>To: Joris Dobbelsteen
>Cc: PostgreSQL general
>Subject: Re: [GENERAL] Delete with subquery deleting all records
>
>Joris Dobbelsteen writes:
>
>> Hint: LEFT JOIN is your mistake...
>
>The use of left join in general.. or my left join?
>When I do the left join by itself I verified manually and it 
>had the data I wanted.

Your usage in this context...

Did you really check your list thoroughly.
>>>SELECT distinct export_messages.export_id as id
>>>FROM export_messages 
>>>>LEFT OUTER JOIN exports ON 
>>>(export_messages.export_id = exports.export_id);

Take any value from "SELECT export_id FROM exports"
Does it not exist in your list?

Try this:
SELECT distinct export_messages.export_id as id,
  exports.export_id as exports_export_id
FROM export_messages 
LEFT OUTER JOIN exports ON 
(export_messages.export_id = exports.export_id)
WHERE exports.export_id IS NOT NULL;

At this point you should know whats going wrong...

>>>DELETE FROM export_messages
>>>WHERE export_id IN
>>>   (SELECT distinct export_messages.export_id as id
>>>FROM export_messages
>>>LEFT OUTER JOIN exports

The LEFT OUTER join will at all times return ALL rows from
export_messages.
In effect, you generate a list with ALL export_messages.export_id. Thus
we must conclude that for every row you are trying to delete, the
condition must evaluate to true.

>>>ON (export_messages.export_id = exports.export_id)
>>>);

>> Thought: are you sure you are going to delete those rows? In there 
>> cases human verification is usually the way to go, though it takes a 
>> lot of time.
>
>If I can't not find a way to do this through SQL I will write 
>a program.
>The time to try and clean this by hand would be countless 
>hours. There are a lot of records in the child table that do 
>not have a matching record in the parent table.

That's the trade-off: effects of a mistake * chance of a mistake against
the cost to prevent these.

Hope this helps...

- Joris


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Delete with subquery deleting all records

[Joris Dobbelsteen]

Hint: LEFT JOIN is your mistake...

Thought: are you sure you are going to delete those rows? In there cases
human verification is usually the way to go, though it takes a lot of
time.

Read on...

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Francisco Reyes
>Sent: donderdag 24 mei 2007 1:12
>To: PostgreSQL general
>Subject: [GENERAL] Delete with subquery deleting all records
>
>I have two tables
>exports
>export_messages
>
>They were done without a foreign key and I am trying to clean 
>the data to put a constraint.
>
>For every record in exports_messages there is supposed to be a 
>matching record in exports with a export_id (ie export_id is 
>the foreign key for
>export_messages) 
>
>The following query identified all records that I need to delete:
>SELECT distinct export_messages.export_id as id FROM 
>export_messages LEFT OUTER JOIN exports ON 
>(export_messages.export_id = exports.export_id);
>
>I checked a number of them.. and all the records returned from 
>that select do not have a matching export_id in exports.
>
>When I try to run:
>delete from export_messages where export_id in (SELECT 
>distinct export_messages.export_id as id FROM export_messages 
>LEFT OUTER JOIN exports ON (export_messages.export_id = 
>exports.export_id) );
>

What seems more resonable:
DELETE FORM export_messages
WHERE NOT export_id IN (SELECT export_id FROM exports)

Make sure you know what you are doing (backup)...

[snip]

- Joris


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Lock table, Select for update and Serialization error

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of sudhir
>Sent: dinsdag 22 mei 2007 19:21
>To: Albe Laurenz
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lock table, Select for update and 
>Serialization error
>
>OK. In your example  lock table command is used to avoid 
>rollbacks due to concurrent transaction.
>So LOCK TABLE is useful in this situation.
>
>I have one last doubt:
>why there is difference between behavior of 'select for 
>update' and 'lock table'.
>one causes serialization error and other does not.
>(even though both are variations of locking mechanism)

The locking level is at a very different level and you have to see the
implications of the diffent ways:

The LOCK statement is to prevent other transactions from accessing the
table. This is a high-level lock with very low overhead to take. The
disadvantage is obviously the performance impact is has, as it is highly
likely to block other transactions.
The mechanism is very useful to get some guarentees about what will
happen with the data in the table. This allows for synchronizing
modification between different transactions.

The select for update has two uses:
1) Ensure the data is current and remains so, for a small subset of a
table.
2) Prevent deadlocks caused by lock escallation.
What I didn't put explicitly is that select for update is to indicate
that a tuple will be updated.
For serializable it implies that the current version you see should be
current.

Obviously there is a common need for something with the concurrency
benefit of "select for update", but with relaxed requirements. The
postgres developers envisioned this and for this purpose use "select for
share".
The select for share only does:
1) Ensure the data is current and remains so, for a small subset of the
table.

Summarizing:
* Lock table - High-level: executes fast, but concurrency problems.
Guarentees about future changes.
Select for update - Low-level, concurrent, ensures data validity and
indicates its modified shortly.
Select for share - Low-level, concurrent, ensures data validity.

Hopefully this clears it up a bit.

- Joris Dobbelsteen

[snip]


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Lock table, Select for update and Serialization error

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of sudhir
>Sent: dinsdag 22 mei 2007 19:28
>To: Tom Lane
>Cc: Albe Laurenz; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lock table, Select for update and 
>Serialization error

[note: text reordered]

>> The critical point here is that LOCK TABLE commands at the 
>start of a 
>> serializable transaction are performed *before* taking the 
>> transaction's snapshot (the snap happens at the first regular DML 
>> command).  They not only protect against post-snap changes 
>as shown by 
>> Albe's example, but against uncommitted changes that were 
>made before 
>> transaction start (by making the serializable xact wait until those 
>> changes are committed or aborted before it takes its snap).
[end reorder]
>It is not necessary that LOCK TABLE will be the first statement.
>(assuming serializable isolation level is snapshot isolation 
>in postgres) For serializable transaction, snapshot should be 
>taken when the 'BEGIN' 
>statement is executed, and not when LOCK TABLE succeeds.

Tom is correct, the snapshot is taken at the first DML statement, NOT at
transaction start (the "begin" statement). Test it yourself.
Your 'should' be might be the expected behaviour, but its not the
implemented behaviour.

As Tom is point out, the LOCK TABLE as the first statement is to prevent
serializable errors from happening.

>Hence, uncommitted changes should be invisible to serializable 
>transaction.

Uncommited changes are at all times only and only visible to the
transaction that made those changes. No other transactions, of any
isolation level, can see uncommited changes from other transactions.
Remember, postgres uses the MVCC model.

- Joris Dobbelsteen


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Data replication through disk replication

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Andrew Sullivan
>Sent: zaterdag 19 mei 2007 15:28
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Data replication through disk replication
>
>On Fri, May 18, 2007 at 05:03:30PM -0700, Ben wrote:
>
>> that all changes are replicated, it won't say an fsync is finished 
>> until it's finished on the remote host too, and it won't let 
>you mount 
>> the block device on the slave system (at least with 0.7x).
>
>How can it guarantee these things?  The web pages say this:
>
>   If the primary node fails, heartbeat is switching the
>   secondary device into primary state and starts the
>   application there. (If you are using it with a non-journaling
>   FS this involves running fsck)
>
>   If the failed node comes up again, it is a new secondary node
>   and has to synchronise its content to the primary. This, of
>   course, will happen whithout interruption of service in the
>   background.
>
>So what happens in those cases where the primary node gets in 
>trouble but isn't actually dead yet?  I see a potential for a 
>race condition here that is really troubling to me.  
>(Especially since it uses the TCP/IP stack, which is 
>notoriously subject to DoS on Linux.)  I think you really had 
>better have something like STONITH running to use this.

General advise you see at linux-ha is to use redundant heartbeat paths.
You can use a serial link if you want to. Other options are redundent
networks. This is to reduce the probability of a split-brain situation.

As you guessed stonith is much required to guard against possible 'race'
conditions caused by hanging nodes.

As a note, DRDB will also do a lot of work for you. It prevents you from
some mistakes you can make.
While starting it waits if it does not detect the other node, and thus
doesn't know who has the latest data available. Can be overriden by a
timeout if desired. (In practice only a single will fail or both will
come up at the same time). Prevents running out of sync.
It also detects when its out of sync, requiring administrator
intervention.

Another advice is to take a look at the linux-ha web sit and mailing
list. Though the web-site might not be the best, the mailing list is
quite active and a lot of knowledge is available.

In general high availability is complex and requires a lot of thought to
cover all possible cases.

[snip]

- Joris Dobbelsteen


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson
>Sent: donderdag 17 mei 2007 22:56
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Fault Tolerant Postgresql (two 
>machines, two postmasters, one disk array)
>
>-BEGIN PGP SIGNED MESSAGE-
>Hash: SHA1
>
>On 05/17/07 09:35, Andrew Sullivan wrote:
>[snip]
>> 
>> The problems come when you get a false detection of machine failure.
>> Consider a case, for instance, where the machine A gets overloaded, 
>> goes into swap madness, or has a billion runaway processes 
>that cause 
>> it to stagger.  In this case, A might not respond in time on the 
>> heartbeat monitor, and then the standby machine B thinks A 
>has failed.  
>> But A doesn't know that, of course, because it is working as hard as 
>> it can just to stay up.  Now, if B mounts the disk and starts the 
>> postmaster, but doesn't have a way to make _sure_ tha A is 
>completely 
>> disconnected from the disk, then it's entirely possible A will flush 
>> buffers out to the still-mounted data area.  Poof!
>> Instant data corruption.
>
>Aren't there PCI heartbeat cards that are independent of the 
>load on the host machine?

A solution commonly seen is to cut the power on the 'failed' machine
just before a take-over is done. Solutions for that are available...

Besides this, you don't want a separate PCI heartbeat card to see if
your software happens to work. Same situation with a watchdog, you don't
want the watchdog to 'reset' itself continuesly, as you loose the
benefit of the watchdog.

Generally your software should also check is postgresql is operation as
expected: its not stopped or non-responsive. In these cases the system
should fail over. The 'cut power' solution works.

If you look for details how to set up, heartbeat (www.linux-ha.org) and
search for stonith. They have lots and lots of very useful information
about high availability solutions. Furthermore the package is used
arround the world for these solutions by large companies and part of
several other software packages. It supports linux and BSD...

- Joris 


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Bruno 
>Wolff III
>Sent: vrijdag 11 mei 2007 21:18
>To: John Gateley
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Fault Tolerant Postgresql (two 
>machines, two postmasters, one disk array)
>
>On Thu, May 10, 2007 at 20:43:20 -0500,
>  John Gateley <[EMAIL PROTECTED]> wrote:
>> Sorry if this is a FAQ, I did search and couldn't find much.
>> 
>> I need to make my Postgresql installation fault tolerant.
>> I was imagining a RAIDed disk array that is accessible from two (or 
>> multiple) computers, with a postmaster running on each computer.
>> (Hardware upgrades could then be done to each computer at different 
>> times without losing access to the database).
>> 
>> Is this possible?
>
>You can't have two postmasters accessing the same data. Doing 
>so will cause corruption. You can have a failover system where 
>another postmaster starts after the normal one has stopped. 
>But you need to be completely sure the normal postmaster has 
>stopped before starting the backup one.

For this you might use heartbeat.
See http://www.linux-ha.org/

They seem to have a good tool to do the job. In general, version 1,
though limited to 2 nodes only is in use for several years and is will
supported with most linux distributions. Also a lot of information is
available on how to set up and getting it to work as desired.
The newer version 2 might provide more features than you actually need.
And since its newer there is fewer use.
I believe heartbeat is also one of the elements in redhats cluster
suite.

- Joris Dobbelsteen


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] syntax error in "createdb"

[Joris Dobbelsteen]

Please state

*   
Which version of postgresql are you using?
*   
Which exact commands are you executing?
*   
What are the exact error messages?

Please be more precise. Your question is, I believe, too vague for the
community to offer good help with your problem.

- Joris



From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of kannan kk
Sent: maandag 7 mei 2007 11:01
To: pgsql-general@postgresql.org; [EMAIL PROTECTED]
Subject: [GENERAL] syntax error in "createdb"


hello

when i try to execute createdb,psql,pg_dump,pg_restore and
similar postgre commands , i get 
syntax at or near 'createdb' and similar to other statements.

plz help me wht would be the problem. 





Office firewalls, cyber cafes, college labs, don't allow you to
download CHAT? Here's a solution!
  

Re: [GENERAL] Audit-trail engine: getting the application's layer user_id

[Joris Dobbelsteen]

 





From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Marcelo de
Moraes Serpa
Sent: dinsdag 24 april 2007 21:06
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Audit-trail engine: getting the
application's layer user_id


Thank you for the replies.

@Richard: I've thought about having one DB user for each APP
user. However, a coworker told me that it would infeasible to do that on
the web enviroment, specifically for J2EE where a DB connection pool is
used, so I gave up on that.  

As Richard mentioned, he has done it.
Remember, for the pool you DO NOT setup a new connection every time but
you can certainly utilize the pool. The trick is the postgresql idea of
the Role-Based Access Control (RBAC) implementation. I.e. you can just
do a SET LOCAL ROLE .
After transaction commit or rollback, or execution of SET LOCAL ROLE
NONE or RESET ROLE you will have your original role (own user) again.
This should work just fine.
 
See also:
http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html

 @Jorge: Is this "connection id" you say equivalent to the
"applicationid" mentioned in the ibm db2 article? If so, how could I get
this data through my application?


On 4/24/07, Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote:


Thank you for the replies.

@Richard: I've thought about having one DB user for each
APP user. However, a coworker told me that it would infeasible to do
that on the web enviroment, specifically for J2EE where a DB connection
pool is used, so I gave up on that. 

@Jorge: Is this "connection id" you say equivalent to
the "applicationid" mentioned in the ibm db2 article? If so, how could I
get this data through my application?

Marcelo. 



On 4/24/07, Jorge Godoy <[EMAIL PROTECTED]> wrote: 

"Marcelo de Moraes Serpa" <[EMAIL PROTECTED]>
writes:

> I forgot to add the link to the article I've
mentioned: 
>
>
http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze
/0302stolze.html#section2b
>
> This is what I'd like to do on PostgreSQL,

So, translating it to a simpler example:

You want that your function gets the
connection ID it is using and
ties it to your current user ID at your
application and then have
all your tables use a trigger to retrieve
the user name from the
auxiliar table that maps "connection ID ->
user", right? 

That's what's in that page: a UDF (user defined
function) named
getapplicationid() that will return the user
login / name / whatever and
triggers.

What is preventing you from writing that?  What
is your doubt with 
regards to how create that feature on your
database?



--
Jorge Godoy  <[EMAIL PROTECTED] >

Re: [GENERAL] New to concurrency

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of John D. Burger
>Sent: zaterdag 7 april 2007 2:04
>To: Postgres General
>Subject: [GENERAL] New to concurrency
>
>For the first time, I find myself wanting to use some of PG's 
>concurrency control stuff, and I could use some advice.
>
>I have requests showing up in a database, and I have one or 
>more servers picking these up with listen/notice.  The 
>requests go into a table with a status column, which is 
>initially NULL, so a server finds requests to process like so:
>
>   begin;
>   select id from requests where status is null order by ts limit 1;
>
>(ts is a timestamp column - process the earliest request first.)
>
>A server takes responsibility for a request by setting the 
>status for the id it got with the previous query:
>
>   update requests set status = 'start' where qid = 42 and 
>status is null;
>   commit;
>
>My client library (Python pgdb) lets me know how many rows got 
>updated, in this case 1, if the server set the status, or 0 if 
>that request's status is no longer NULL, like if some other 
>server picked it up in the interim.
>
>Now I know that this last bit is not really any protection 
>against two servers both thinking they should process the same 
>request.  I suspect I want to use SELECT FOR UPDATE in the 
>first query - will that be sufficient in this situation?

That should indeed work. SELECT FOR UPDATE will take an exclusive lock
on the row, conflicting with any other lock. There could be some
resource contention though...

There are a few more tricks and this must be handled with extreme care.
Obviously the most simple solution is to have a single agent handle
dispatching of tasks, making this at least less prone to mistakes or
oversights.

BEGIN;
CREATE TABLE request (
id serial NOT NULL PRIMARY KEY,
status character varying(10) DEFAULT NULL
);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
INSERT INTO request(status) VALUES (NULL);
COMMIT;

Now we start the request with status NULL and the earliest ID (similar
to your timestamp). We do this for two transactions: T1 and T2...

T1: BEGIN;
T2: BEGIN;
T1: SELECT id FROM request WHERE status IS NULL ORDER BY id LIMIT 1 FOR
UPDATE;
T2: SELECT id FROM request WHERE status IS NULL ORDER BY id LIMIT 1 FOR
UPDATE;
-- T2 will block now...
T1: UPDATE request SET status = 'start' WHERE id = 1;
T1: COMMIT;
T2: Will return no value at this point (8.2.1), so T2 will have to
retry.

If you want to avoid retries a slightly different approach is required.
You can do it similar to how its normally done in other software: Take a
'global' exclusive lock for dequeuing. Another table or row can be
designated for synchronization (do SELECT FOR UPDATE or LOCK TABLE
EXCLUSIVE on it).
If you have only low volume, even a exclusive table lock will work.
I'm not sure whether you can do it correctly with an advisory lock, as
its not released at commit time, but require you to do so explicitly.
This seems to give a risk of requiring a retry or an additional
transaction.
Note that this requires READ COMMITED isolation level, a serializable
lock will result in 'can't serialize' errors and one must do retries.

Probably you want to wrap it up in a stored procedure (plpgsql), as this
makes the process go much faster due to a reduction in round-trips.
Fewer round-trips mean less time is required during which the lock is
held, reducing contention.

Obviously the database is still no match for an purposely build
dispatcher, as it can control its locks with much finer (time)
granularity and the overhead is much much smaller (e.g. transaction
setup/commit and relative complex locking protocol).

- Joris


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Brandon Aiken
>Sent: woensdag 21 maart 2007 15:25
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lifecycle of PostgreSQL releases 
>
[snip]
>Software *always* has bugs.

Sorry, couldn't resist...

Software Engineer: "My salary payments didn't get through this month, I
still haven't received any of you".
Accountant (former Software Engineer): "Sorry, our systems indicate that
your payment tripped a fatal bug in our payout software. That can
happen, you know, software has bugs after all. It's not problem, its
only a minor one. Well, we'll try again next month, maybe that works."

Could people for once treat bugs as unacceptable instead an accepted
thing?
(Especially people writing software for validating the software we are
writing is correct.)

As I said, I couldn't resist. Sorry...

- Joris

[snip]


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Design / Implementation problem

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Naz Gassiep
>Sent: zondag 18 maart 2007 14:45
>To: Naz Gassiep
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Design / Implementation problem
>
>Here it is again with more sensible wrapping:
>
>
>*** The Scenario ***
>
>We are running a customer loyalty program whereby customers 
>earn points for purchasing products. Each product has a value 
>of points that are earned by purchasing it, and a value of 
>points required to redeem it.
>
>In order to prevent customers from stockpiling points, we want 
>to place an expiry date on points so that unused points expire 
>and are lost if they are not redeemed within a certain period 
>of time. This will be calculated on a FIFO basis, I.e., the 
>oldest points will expire first.
>
>We will assume the expiry period is 12 months.
>
>
>*** The Problem ***
>
>Ascertaining which points to expire is fairly conceptually 
>simple. At any given point in time, the points expired is 
>simply the balance on hand at the start of the period, less 
>redemptions in that period. If the redemptions is less than 
>the balance at open, not all points that were available on 
>that date were used, and the difference is the expiry.
>
>This can be done periodically, say, at the start of every 
>month. However there are a few problems with doing it periodically
>
>1. The runs are likely to be too large to be manageable. A DB 
>with tens of thousands of customers and many hundreds of 
>thousands or even millions of sales in the records tables will 
>require several queries and some application calculation to 
>compute. If it takes 2 seconds to compute each balance of a 
>20,000 strong customer base, that's over 11 hours of heavy 
>lifting in the DB, which will likely result in severely 
>degraded performance during those hours. This problem can only 
>get worse as time goes on, and hardware upgrade requirements 
>just to accommodate a 12 hour window once a month is the sign 
>of an app not designed to scale well.
>
>2. Calculating the balance on the fly would be more effective, 
>as it is unlikley that many customers will check their balance 
>on a regular basis.
>It is likely that a small fraction of customers will check 
>their balance in a given month, meaning that calculating it on 
>the fly would both spread the load over time as well as reduce 
>the total load, even if on the fly calculation results in 
>significantly higher per-customer calculation time.
>
>3. The app is a web app, and it would be preferable to contain 
>business logic within the database itself or the current app 
>codebase. Spreading application logic into an external 
>mechanism such as cron or an external daemon would be 
>undesirable unless there was no other way.
>
>
>*** A Possible Solution ***
>
[snip]
>
>*** The Question ***
>
>Is there a way to design the DB schema as well as the query in 
>such a manner that calculating the point balance on the fly is 
>not an unfeasibly heavy duty calculation to be done at every page view?

*** My Answer ***

I could think of a simple solution that might work, at the cost of a
little storage space. This gives an advantage in computational overhead.

For every time you award points, track two things:
* Awarded points...
* Points remaining from the awarded ones.
  Obviously equal to awarded points at insertion time
* Date they are awarded (or the expirary date, that doesn't matter).

When you are subtracting points just update the the non-expired
remaining points, with the oldest first.

From the problem I think you can do it on-the-fly without too much
overhead. You can plug in your scheme how to account the points:
Per-order, add to the order table...
Per-period, add a table for the points only...

Of course it really depends on how much data you are expecting. Overhead
will be 'fixed' for per-period and otherwise scale with orders/customer.

[snip]

Maybe this helps a bit,

- Joris


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] sql formatter/beautifier

[Joris Dobbelsteen]

If you use pgAdmin3 for example, it will format the definition in the
database for you. This will exclude stored procedures, which are stored
as-is.

The formatter isn't too good however... 

There are better products on the market that will do the formatting
significantly better.
This one was nice: http://psti.equinoxbase.com/cgi-bin/handler.pl

- Joris

>-Original Message-
>From: Aaron Bingham [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 8 maart 2007 11:36
>To: Joris Dobbelsteen
>Cc: Merlin Moncure; postgres general
>Subject: Re: [GENERAL] sql formatter/beautifier
>
>Joris Dobbelsteen wrote:
>> PostGreSQL (7.4 and onward) has such a thing build-in, but its not 
>> particulary good (simple case works, but once it gets 
>complex it makes 
>> a mess out of it).
>Hi,
>
>Cleaning out my pgsql-general mail, I ran across your post.  
>How do I invoke PostgreSQL's built-in SQL beautifier?  I 
>wasn't able to find a reference to this feature in the documentation.
>
>Thanks,
>
>--
>
>Aaron Bingham
>Senior Software Engineer
>Cenix BioScience GmbH
>


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Why don't dumped files parse in pgAdmin3 query editor?

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>[EMAIL PROTECTED]
>Sent: maandag 5 maart 2007 16:28
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Why don't dumped files parse in pgAdmin3 
>query editor?
>
>Here's something I've always wondered. When you dump a 
>database, the dumped file looks like ordinary SQL, but if I 
>load it into a query editor window and try to execute it, I 
>always get syntax errors. The specific errors vary, but it 
>always makes it impossible to reload the data that way.
>
>In the past I've just used psql -f [file] [schema] to reload 
>them, but I'm trying to do this on Windows, and I can't seem 
>to get anything to work from the command shell, so I'm really stuck.

It seems it has some strange syntax that might be psql-specific, perhaps
its even for linux only.

However, it might make some difference if you dump with "Use insert
statements" instead of the normal copy from stdin. This seemed to work
for me last time. Your mileage may vary.

- Joris 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] supporting 2000 simultaneous connections.

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Shiva Sarna
>Sent: vrijdag 2 maart 2007 6:03
>To: Bill Moran; Joshua D. Drake
>Cc: Shiva Sarna; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] supporting 2000 simultaneous connections.
>
>Hi,
>
>Thanks for your reply and help.
>
>The web application I am talking about is a Learning 
>Management System. 
>
>By simultaneous users I mean that all 2000 users should be 
>able to access the application and for example say take a test.

As Bill Moran already said:
"You might want to take some time to investigate what "simultaneous
users" really means for your application.  For example, in a web
application, 2000 simultaneous users usually equates to less than 100
actual database connections, as web users spend most of their time
reading pages and very little time actually talking to the database."
This should also be true for your application too, especially since it's
a web application. You don't need a persistent connection per user, but
can rather share then between different users.

When taking tests I'm not that fast and reading, thinking and clicking
will take some seconds. Your system is well capable of storing my
answers quickly enough (and probably getting the question as well).

>We are using JNDI connection pooling.
>
>I think we have to now think about upgrading the hardware as well.

Test this first with you existing setup.
Remember, 2000 web clients is not the same as 2000 database connections!
Your question was if most likely interpreted whether postgres can
sustain 2000 connections to the database at once, rather than whether it
can handle 2000 web users.

- Joris

>thanks for your time.
>
>regards
>
>Shiva
>
>
>--- Bill Moran <[EMAIL PROTECTED]> wrote:
>
>> In response to "Joshua D. Drake"
>> <[EMAIL PROTECTED]>:
>> 
>> > Bill Moran wrote:
>> > > In response to Shiva Sarna
>> <[EMAIL PROTECTED]>:
>> > > 
>> > >> Hi,
>> > >>
>> > >> I am working on a web application where the
>> front end is struts framework
>> > >> and back end is PgSQL 7.4.
>> > 
>> > *cough*, you are going to greatly decrease your
>> ability to scale if you
>> > are running anything less than 8.1.
>> > > 
>> > > Performance _will_ degrade if all of those
>> connections are busy at once, but
>> > > that's going to happen with any shared system. 
>> The disk can only read from
>> > > one area at a time, and other system resources
>> will be contended for as well.
>> > 
>> > 7.4 doesn't scale to what he wants, even on big
>> hardware.
>> 
>> Oops ... didn't notice that.
>> 
>> --
>> Bill Moran
>> Collaborative Fusion Inc.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL

[Joris Dobbelsteen]

See the discussion "[GENERAL] Database versus filesystem for storing
images" earlier on the List.
It started at 31 december 2006 and ended 9 januari 2007.
 
It goes trough all/most pro/con arguments for different options.
 
- Joris




From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Alain Roger
Sent: zondag 25 februari 2007 16:57
To: pgsql-general@postgresql.org
Subject: [GENERAL] Best way to store and retrieve photo from
PostGreSQL


Hi,

I would like to store picture in my DB and after to display them
on my PHP pages.

What is the best solution for that ?

thanks a lot

-- 
Alain
 
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5 

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] 
>Sent: vrijdag 23 februari 2007 9:50
>To: Joris Dobbelsteen
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Fri, Feb 23, 2007 at 12:41:25AM +0100, Joris Dobbelsteen wrote:
>> >Reasonably. I have no idea what visibility rules would make any 
>> >difference at all. AIUI a foreign key just takes a shared 
>lock on the 
>> >referenced row and all the magic of MVCC makes sure the row exists 
>> >when the transaction completes.
>> 
>> Try this:
>> (sorry for any typo's in SQL, if they exist)
>
>
>
>Well, I took a look at the RI code and the only stuff I saw 
>that looked interesting was this:
>
>utils/adt/ri_triggers.c:
>if (IsXactIsoLevelSerializable && detectNewRows)
>{
>CommandCounterIncrement();  /* be 
>sure all my own work is visible */
>test_snapshot = CopySnapshot(GetLatestSnapshot());
>crosscheck_snapshot = 
>CopySnapshot(GetTransactionSnapshot());
>}
>
>It then proceeds to use that snapshot to execute the query to 
>get the share lock.
>
>It's probably true that other PL's can't do this directly. Not 
>sure how to deal with that. I got confused because I thought 
>the first version of RI did use straight pl/pgsql functions, 
>so I thought that was enough.

You got it right...

/*
 * SPI_execute_snapshot -- identical to SPI_execute_plan, except that we
allow
 * the caller to specify exactly which snapshots to use.  This is
currently
 * not documented in spi.sgml because it is only intended for use by RI
 * triggers.
 *
 * Passing snapshot == InvalidSnapshot will select the normal behavior
of
 * fetching a new snapshot for each query.
 */
int
SPI_execute_snapshot(void *plan,
 Datum *Values, const char
*Nulls,
 Snapshot snapshot, Snapshot
crosscheck_snapshot,
 bool read_only, long tcount)

They got the point right: only intended for use by RI triggers. That's
exactly the type I'm trying to build ;)
They are exposed to the C versions (its in include/executor/spi.h), but
to me it looks a bit cumbersome to have triggers written in C.

What would be a good way to expose this to normal PL triggers? Since
this would open a new set of possibilities...

As part of a "create trigger ... for referencial integrity"?
As an extension to a statement?
Special construct in the languages?

- Joris

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Stephan Szabo [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 23:13
>To: Joris Dobbelsteen
>Cc: Martijn van Oosterhout; Robert Haas; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, 22 Feb 2007, Joris Dobbelsteen wrote:
>
>> >-Original Message-
>> >From: [EMAIL PROTECTED]
>> >[mailto:[EMAIL PROTECTED] On Behalf Of 
>Martijn van 
>> >Oosterhout
>> >Sent: donderdag 22 februari 2007 18:17
>> >To: Joris Dobbelsteen
>> >Cc: Robert Haas; pgsql-general@postgresql.org
>> >Subject: Re: [GENERAL] complex referential integrity constraints
>> >
>> >On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote:
>> >> Even worse, I don't you can guarentee that this constraint
>> >is enforced
>> >> at all times. That means, not if you are using triggers.
>> >> The only option seems using foreign keys and put in a lot of
>> >redundant
>> >> data.
>> >
>> >Err, foreign keys are implemented using triggers, so this statement 
>> >is self-contradictary.
>>
>> Are you really sure they are executed under the same 
>visibility rules?
>
>IIRC, the ri triggers use calls that you aren't able to get at 
>in triggers written in any of the PLs, but I think you should 
>be able to replicate the feat in a trigger written in C.

Why they never did that? No need or no pratical situations where it went
wrong?

IMHO it should become possible to expose this functionality to the PL
languages or as part of the trigger system? The current system can be
shown to be underpowered to enforce constraints. It seems a bit
cumbersome to have C functions for this purpose.

However I must admit that looking through the postgres code doesn't make
it much clearer to me whats actually going for these kind of tricky
problems...

- Joris

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 23:15
>To: Joris Dobbelsteen
>Cc: Robert Haas; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, Feb 22, 2007 at 06:51:49PM +0100, Joris Dobbelsteen wrote:
>> >Err, foreign keys are implemented using triggers, so this statement 
>> >is self-contradictary.
>> 
>> Are you really sure they are executed under the same 
>visibility rules?
>
>Reasonably. I have no idea what visibility rules would make 
>any difference at all. AIUI a foreign key just takes a shared 
>lock on the referenced row and all the magic of MVCC makes 
>sure the row exists when the transaction completes.

Try this:
(sorry for any typo's in SQL, if they exist)

CREATE TABLE a (val integer NOT NULL PRIMARY KEY);
CREATE TABLE b (val integer NOT NULL, val2 integer NOT NULL, PRIMARY KEY
(val, val2);
-- we will be doing foreign key ourselves
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (1,1);
INSERT INTO a VALUES (2,2);

-- Now two transaction (T1, T2)
T1: BEGIN ISOLATION LEVEL SERIALIZABLE;
T2: BEGIN ISOLATION LEVEL SERIALIZABLE;
-- Lets see what we have got.
T1: SELECT * FROM a;
T1: SELECT * FROM b;
T2: SELECT * FROM a;
T2: SELECT * FROM b;
-- lets insert something...
T2: INSERT INTO a VALUES (2,100);
-- results in a lock being acquired
T2: SELECT 1 FROM a x WHERE val = 2 FOR SHARE ON x; -- this is your
lock
-- Ok, done for now...
T2: COMMIT; -- now the lock is gone
-- This means T1 doesn't see the row, right?
T1: SELECT * FROM b;
-- now lets delete
T1: DELETE FROM a WHERE val = 2;
-- on cascade delete, thus:
T1: DELETE FROM b WHERE val = 2;-- won't see new tuple
(serializable isolation)
T1: COMMIT;

SELECT * FROM b;
val val2
2   100

Sorry, constraint wasn't enforced ;)
It does matter.

Now try it with this:
CREATE TABLE b (val integer NOT NULL, val2 integer NOT NULL, PRIMARY KEY
(val, val2), FOREIGN KEY val REFERENCES a(val) ON UPDATE CASCADE ON
DELETE CASCADE);
That won't inhibit this behaviour, but proberly enforces the constraint
(as one would have expected). I believe T2 will abort as in the manual.

Your statement might be correct, but it doesn't take enough account of
how the visibility rules under MVCC are played. It seems the foreign
keys (as well as primary keys) have there rules applied differently,
they see that row and will cause an abort.

- Joris

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Martijn van Oosterhout
>Sent: donderdag 22 februari 2007 18:17
>To: Joris Dobbelsteen
>Cc: Robert Haas; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Thu, Feb 22, 2007 at 05:28:35PM +0100, Joris Dobbelsteen wrote:
>> Even worse, I don't you can guarentee that this constraint 
>is enforced 
>> at all times. That means, not if you are using triggers.
>> The only option seems using foreign keys and put in a lot of 
>redundant 
>> data.
>
>Err, foreign keys are implemented using triggers, so this 
>statement is self-contradictary.

Are you really sure they are executed under the same visibility rules?

- Joris

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Guarenteeing ordering constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Tom Lane [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 17:16
>To: Joris Dobbelsteen
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Guarenteeing ordering constraints 
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>> I have some trouble guarenteeing that an ordering constraint is 
>> enforced on the database. On the table ordering (see below) 
>I want to 
>> enforce that for every tuple t, all tuples u where u.position < 
>> t.position this implies u.cumvalue <= t.cumvalue.
>
>I can't think of any reasonable way to enforce that in SQL.  
>Perhaps you should consider restructuring your tables in such 
>a way that this behavior emerges from a constraint that is 
>enforceable --- maybe the cumulative values should be a 
>(materialized?) view on an underlying table that contains 
>individual observations.

Mmm, it seems that I'm beyond the boundaries. I seem to have the habit
of wanting just a little more than is possible. Nevertheless this seemed
simple enough...

Of course there is no way to force the trigger to read all commited rows
and be waiting on the (possibly) required uncommitted ones? This may get
triggers closer to the constraints (like unqiue indexes and foreign
keys).
Seems triggers are more for business intelligence...

Thinking over the idea, I strongly believe restructing is going to cause
more trouble than anything else. Beyond that, there are now enough
constraints that I'm sure I cannot enforce anyways. That is, I cannot
even think about a good way to do it.
The current triggers will prevent bad things most of the time, but not
always. At this point that seems good enough for practical situations.

Example:
You have a printer, that's of some kind of model.
With the model you define what cartridges (colors) you can put in.
You can now replace a cartridge on a printer.
Of course you want to enforce that the color of the cartridge you
replace is valid.

Same problem, and I'm quite sure I cannot enforce it.

I believe something fundamental should change to ensure we can enforce
these constraints. At least, that would be a lot nicer.

- Joris

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: Robert Haas [mailto:[EMAIL PROTECTED] 
>Sent: donderdag 22 februari 2007 15:58
>To: Joris Dobbelsteen; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>The ability to make a foreign key reference a specific partial 
>unique index (rather than just a set of columns that have a 
>unique index) would solve many problems of this type.  As 
>another example, you might have a table where one of the 
>columns is "is_deleted boolean not null".  By creating a 
>partial unique index on the primary key of that table "WHERE 
>NOT is_deleted" and then pointing a foreign key at it, you 
>could enforce that each row in the child table references a 
>parent who isn't deleted.
>
>However, this would break down when there's more one than 
>intermediate step involved.  For example, if you have:
>
>CREATE TABLE animal_type (
>  id   serial,
>   namevarchar(60) not null,
>   is_attacker boolean not null,
>   primary key (id)
>);
>
>CREATE TABLE animal (
>   id  serial,
>   type_id integer not null references animal_type (id),
>   namevarchar(60) not null,
>   primary key (id)
>);
>
>CREATE TABLE mauling (
>   id  serial,
>   attacker_id integer not null references animal (id),
>   victim_id   integer not null references animal (id),
>   attack_time timestamp with time zone not null,
>   primary key (id)
>);
>
>It would be easy to enforce the constraint that the attacker 
>must be an animal of some specific type, but difficult to 
>enforce the constraint that the attacker must be an animal 
>whose type, in turn, has a true value for is_attacker.

Even worse, I don't you can guarentee that this constraint is enforced
at all times. That means, not if you are using triggers.
The only option seems using foreign keys and put in a lot of redundant
data.

>The best idea that I can think of right now to handle multiple 
>levels of tables is to allow FOREIGN KEY constraints to 
>references a VIEW, rather than a table.  Then you could say:
>
>CREATE VIEW attackers AS
>SELECT a.id FROM animal a, animal_type t WHERE a.type_id = 
>t.id AND t.attacker;
>
>...and then FOREIGN KEY (attacker_id) REFERENCES attackers (id).

Perhaps "Alban Hertroys" idea solves this problem a little easier.
However it lacks the possibility to make quick changes later on
(predator is a predator, or you are screwed, no second change).
This is not acceptable in problems where such things are decided after
object creation or might be changed later on.

>This syntax would solve a number of other problems as well, 
>such as requiring that some record in table A has a parent 
>either in table P or in table Q. However, I think this would 
>probably require implementing some kind of materialized view 
>so that you could actually build an index on the view, and 
>that opens up a whole new can of worms, because it's not very 
>difficult to define a view that is costly to update incrementally.

You don't need a materialized view to put a database to its knees. You
can already do that today, with ease. I wouldn't worry too much about
that.
If you mean from a syntax I suggested I do not believe it's the 'right'
way to define an (materialized) view, rather use a trigger-like style of
system. There are some other issues, however.

>The problem is really that there is a pretty large gap between 
>writing a foreign key constraint, which is trivial, and 
>enforcing a constraint using triggers, which is quite a bit 
>more complex (and therefore, easy to screw up), because the 
>foreign key automatically handles all the cases (insert into 
>child table, update of child table, update of parent table, 
>delete from parent table) whereas with triggers you have to 
>address each of those cases individually.

Exactly, that is why I suggested such a system. If its not easy to
enforce constraints, it will never happen properly. Especially if
problems get more complex.

>Unfortunately, 
>something tells me that implementing a more powerful system 
>for foreign key constraints is a non-trivial project, however 
>useful it would be.
>Still, I'd love to see it in the TODO file, too.

Me too, I get the impression that SQL is too weak for most constraints.

- Joris

>...Robert
>
>-Original Message-
>From: Joris Dobbelsteen [mailto:[EMAIL PROTECTED]
>Sent: Thursday, February 22, 2007 8:03 AM
>To: Robert Haas; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE

Re: [GENERAL] Guarenteeing ordering constraints

[Joris Dobbelsteen]

Even this can be violated.
Just create another table and change the first select statement of the
transactions to get data from that table.

Is there any way to actually enforce such ordering constraints under
postgresql?

- Joris

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Joris 
>Dobbelsteen
>Sent: donderdag 22 februari 2007 14:27
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Guarenteeing ordering constraints
>
>I have some trouble guarenteeing that an ordering constraint 
>is enforced on the database. On the table ordering (see below) 
>I want to enforce that for every tuple t, all tuples u where 
>u.position < t.position this implies u.cumvalue <= t.cumvalue.
>
>Unfortunally postgresql gives me a choice between concurrency 
>or consistency. Given the trigger procedure below (written for 
>simplicity, not speed) it will fail to guarentee consistency 
>when using serializable isolation.
>Just load the initial dataset (the failing operations are only 
>to test the constraints).
>The next step is to execute transactions 1 and 2 in parallel 
>(step by step). This will cause the constraint to be violated.
>
>It does work in the default isolation level (read committed).
>Alternatively one can use the second LOCK statement in the 
>trigger, which does an ACCESS EXCLUSIVE. Obviously this 
>completely defeats concurrency (causes one of the transactions 
>to be retried).
>
>Is there any way that will both guarentee consistency and 
>provide some better concurrency?
>
>- Joris
>
>===
>
>CREATE PROCEDURAL LANGUAGE plpgsql;
>
>CREATE TABLE ordering (
>"position" integer NOT NULL,
>cumvalue integer NOT NULL
>);
>
>CREATE FUNCTION tr_ordering_cumvalue_simple() RETURNS "trigger"
>AS $$BEGIN
>   -- position is unique (index enforced)
>   -- cumvalue constraint
>   --
>   -- Let p,q be an element of ordering,
>   -- where p.position < q.position implies p.cumvalue <= 
>q.cumvalue
>
>   -- Thus for every new tuple t
>   -- we need to ensure
>   -- For all p (of ordering) p.position < t.position 
>implies p.cumvalue <= t.cumvalue
>   --  andp.position > t.position implies
>p.cumvalue >= t.cumvalue
>   --
>   -- note (p implies q) <=> (!p or q)
>
>   -- lock full table, no others updating it...
>   LOCK TABLE ordering IN EXCLUSIVE MODE;
>   --LOCK TABLE ordering IN ACCESS EXCLUSIVE MODE;
>
>   IF EXISTS (SELECT *
>  FROM ordering o
>  WHERE-- violates constraints
>   (o.position < NEW.position and o.cumvalue >
>NEW.cumvalue)
>  OR
>   (o.position > NEW.position and o.cumvalue <
>NEW.cumvalue)
> )
>   THEN
>   RAISE EXCEPTION 'Constraint violation detected 
>by %', TG_name;
>   END IF;
>
>   RETURN NEW;
>END$$
>LANGUAGE plpgsql;
>
>CREATE TRIGGER tr_ordering_cumvalue
>BEFORE INSERT OR UPDATE ON ordering
>FOR EACH ROW
>EXECUTE PROCEDURE tr_ordering_cumvalue_simple();
>
>
>-- initial dataset
>BEGIN;
>DELETE FROM ordering;
>INSERT INTO ordering VALUES (0,0);
>INSERT INTO ordering VALUES (10,100);
>INSERT INTO ordering VALUES (20,200);
>COMMIT;
>
>-- failing operation
>BEGIN;
>INSERT INTO ordering VALUES (-1,1);
>INSERT INTO ordering VALUES (15,1);
>INSERT INTO ordering VALUES (16,201);
>INSERT INTO ordering VALUES (21,-1);
>ROLLBACK;
>
>-- transaction 1
>BEGIN ISOLATION LEVEL SERIALIZABLE;;
>SELECT * FROM ordering;
>INSERT INTO ordering VALUES (19,101);
>SELECT * FROM ordering;
>COMMIT;
>
>-- transaction 2
>BEGIN ISOLATION LEVEL SERIALIZABLE;
>SELECT * FROM ordering;
>INSERT INTO ordering VALUES (11,199);
>SELECT * FROM ordering;
>COMMIT;
>
>---(end of 
>broadcast)---
>TIP 6: explain analyze is your friend
>

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Guarenteeing ordering constraints

[Joris Dobbelsteen]

I have some trouble guarenteeing that an ordering constraint is enforced
on the database. On the table ordering (see below) I want to enforce
that for every tuple t, all tuples u where u.position < t.position this
implies u.cumvalue <= t.cumvalue.

Unfortunally postgresql gives me a choice between concurrency or
consistency. Given the trigger procedure below (written for simplicity,
not speed) it will fail to guarentee consistency when using serializable
isolation.
Just load the initial dataset (the failing operations are only to test
the constraints).
The next step is to execute transactions 1 and 2 in parallel (step by
step). This will cause the constraint to be violated.

It does work in the default isolation level (read committed).
Alternatively one can use the second LOCK statement in the trigger,
which does an ACCESS EXCLUSIVE. Obviously this completely defeats
concurrency (causes one of the transactions to be retried).

Is there any way that will both guarentee consistency and provide some
better concurrency?

- Joris

===

CREATE PROCEDURAL LANGUAGE plpgsql;

CREATE TABLE ordering (
"position" integer NOT NULL,
cumvalue integer NOT NULL
);

CREATE FUNCTION tr_ordering_cumvalue_simple() RETURNS "trigger"
AS $$BEGIN
-- position is unique (index enforced)
-- cumvalue constraint
--
-- Let p,q be an element of ordering,
-- where p.position < q.position implies p.cumvalue <=
q.cumvalue

-- Thus for every new tuple t
-- we need to ensure
-- For all p (of ordering) p.position < t.position implies
p.cumvalue <= t.cumvalue
--  andp.position > t.position implies
p.cumvalue >= t.cumvalue
--
-- note (p implies q) <=> (!p or q)

-- lock full table, no others updating it...
LOCK TABLE ordering IN EXCLUSIVE MODE;
--LOCK TABLE ordering IN ACCESS EXCLUSIVE MODE;

IF EXISTS (SELECT *
   FROM ordering o
   WHERE-- violates constraints
(o.position < NEW.position and o.cumvalue >
NEW.cumvalue)
   OR
(o.position > NEW.position and o.cumvalue <
NEW.cumvalue)
  )
THEN
RAISE EXCEPTION 'Constraint violation detected by %',
TG_name;
END IF;

RETURN NEW;
END$$
LANGUAGE plpgsql;

CREATE TRIGGER tr_ordering_cumvalue
BEFORE INSERT OR UPDATE ON ordering
FOR EACH ROW
EXECUTE PROCEDURE tr_ordering_cumvalue_simple();


-- initial dataset
BEGIN;
DELETE FROM ordering;
INSERT INTO ordering VALUES (0,0);
INSERT INTO ordering VALUES (10,100);
INSERT INTO ordering VALUES (20,200);
COMMIT;

-- failing operation
BEGIN;
INSERT INTO ordering VALUES (-1,1);
INSERT INTO ordering VALUES (15,1);
INSERT INTO ordering VALUES (16,201);
INSERT INTO ordering VALUES (21,-1);
ROLLBACK;

-- transaction 1
BEGIN ISOLATION LEVEL SERIALIZABLE;;
SELECT * FROM ordering;
INSERT INTO ordering VALUES (19,101);
SELECT * FROM ordering;
COMMIT;

-- transaction 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM ordering;
INSERT INTO ordering VALUES (11,199);
SELECT * FROM ordering;
COMMIT;

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

I partially agree:
If people CAN do stupid things, they are 'clever' enough to find a way
to actually do it. I've seen them destroy things, by just using a system
in a way it was not intended. They effectively found a way to blow away
the very thing that part was designed for.
But indeed, it's a lot of work, especially if the number of tables that
must be referenced increases. I'm a strong supporter for ensuring
consistency. Postgres has what it takes to do the job, but it doesn't
make my life a lot easier. But it seems to be as good as it gets
today...

Perhaps we should rather define a 'database' constraint in the order of:
"For every mauling, the attacking animal must be of the attacker type"
(in a computer understandable manner). From the set theory this should
be possible without too much problems, However doing so efficiently
might be slightly harder.
This might be a fun project and useful for the TODO list. At least it
makes it a lot easier (and maintanable) to enforce database-wide
constraints.

- Joris

>-Original Message-
>From: Robert Haas [mailto:[EMAIL PROTECTED] 
>Sent: woensdag 21 februari 2007 3:37
>To: Joris Dobbelsteen; elein
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>Yes, exactly.  And while you might not care about all of those 
>(e.g. I care about the first two but am not worried about the 
>third one because I'm the only one who will ever update that 
>table), writing multiple triggers to enforce each constraint 
>of this type quickly gets old if there are even a few of them. 
> It is exponentially harder to write a constraint of this type 
>than it is to write a simple foreign key constraint.
>
>...Robert 
>
>-Original Message-
>From: Joris Dobbelsteen [mailto:[EMAIL PROTECTED]
>Sent: Monday, February 19, 2007 5:59 AM
>To: elein; Robert Haas
>Cc: pgsql-general@postgresql.org
>Subject: RE: [GENERAL] complex referential integrity constraints
>
>>Why don't you add a field in animal_types that is boolean mauler.
>>Then you can add a trigger on the mauling table to raise an 
>error when 
>>the attacker_id is an animal type mauler.
>
>This is only partial. You need a lot more triggers to 
>guarentee the constraints are enforced.
>Precisely you need to validate:
>* mauling on insert/update of attacker_id
>* animal on update of type_id
>* animal_type on update of your property
>
>Of course you need to think about the MVCC model, such that:
>Transaction 1 executes
>INSERT INTO mauling VALUES ('someattacker'), Transaction 2 
>executes UPDATE animal_type SET mauler = false WHERE name = 
>'someattacker', such that both transaction happen in parallel.
>
>This is perfectly possible and will make it possible to 
>violate the constraint, UNLESS locking of the tuples is done correctly.
>
>These contraints are not trivial to implement (unfortunally). 
>It would be great if they where.
>
>- Joris
>

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] complex referential integrity constraints

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of elein
>Sent: zondag 18 februari 2007 23:16
>To: Robert Haas
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] complex referential integrity constraints
>
>On Fri, Feb 16, 2007 at 09:58:56AM -0500, Robert Haas wrote:
>> So, I have the following problem.
>> 
>> Suppose you have two kinds of animals, sheep and wolves.  Since they 
>> have very similar properties, you create a single table to hold both 
>> kinds of animals, and an animal_type table to specify the 
>type of each
>> animal:
>> 
>> CREATE TABLE animal_type (
>> id   integer not null,
>> name varchar(80) not null,
>> primary key (id)
>> );
>> INSERT INTO animal_type VALUES (1, 'Sheep'); INSERT INTO animal_type 
>> VALUES (2, 'Wolf');
>> 
>> CREATE TABLE animal (
>> id   serial,
>> type_id integer not null references 
>animal_type (id), 
>> name varchar(80) not null,
>> age  integer not null,
>> weight_in_pounds integer not null,
>> primary key (id)
>> );
>> 
>> The animal_type table is more or less written in stone, but 
>the animal 
>> table will be updated frequently.  Now, let's suppose that 
>we want to 
>> keep track of all of the cases where one animal is mauled by another
>> animal:
>> 
>> CREATE TABLE mauling (
>> id  serial,
>> attacker_id integer not null references animal (id),
>> victim_id   integer not null references animal (id),
>> attack_time timestamp not null,
>> primary key (id)
>> );
>> 
>> The problem with this is that I have a very unsettled feeling about 
>> the foreign key constraints on this table.  The victim_id constraint 
>> is fine, but the attacker_id constraint is really 
>inadequate, because 
>> the attacker CAN NEVER BE A SHEEP.  I really want a way to write a 
>> constraint that says that the attacker must be an animal, but 
>> specifically, a wolf.
>> 
>> It would be really nice to be able to write:
>> 
>> FOREIGN KEY (attacker_id, 2) REFERENCES animal (id, type_id)
>> 
>> Or:
>> 
>> CREATE UNIQUE INDEX wolves ON animal (id) WHERE type_id = 2;
>> -- and then
>> FOREIGN KEY (attacker_id) REFERENCES INDEX wolves
>> 
>> ...but that's entirely speculative syntax.  I don't think 
>there's any 
>> easy way to do this.  (Please tell me I'm wrong.)
>> 
>> The problem really comes in when people start modifying the animal 
>> table.  Every once in a while we have a case where we record 
>something 
>> as a wolf, but it turns out to have been a sheep in wolf's 
>clothing.  
>> In this case, we want to do something like this:
>> 
>> UPDATE animal SET type_id = 1 WHERE id = 572;
>> 
>> HOWEVER, this operation MUST NOT be allowed if it turns out 
>there is a 
>> row in the mauling table where attacker_id = 572, because that would 
>> violate my integrity constraints that says that sheep do not maul.
>> 
>> Any suggestions?  I've thought about creating rules or triggers to 
>> check the conditions, but I'm scared that this could either (a) get 
>> really complicated when there are a lot more tables and constraints 
>> involved or
>> (b) introduce race conditions.
>
>Why don't you add a field in animal_types that is boolean mauler.
>Then you can add a trigger on the mauling table to raise an 
>error when the attacker_id is an animal type mauler.

This is only partial. You need a lot more triggers to guarentee the
constraints are enforced.
Precisely you need to validate:
* mauling on insert/update of attacker_id
* animal on update of type_id
* animal_type on update of your property

Of course you need to think about the MVCC model, such that:
Transaction 1 executes
INSERT INTO mauling VALUES ('someattacker'),
Transaction 2 executes
UPDATE animal_type SET mauler = false WHERE name = 'someattacker',
such that both transaction happen in parallel.

This is perfectly possible and will make it possible to violate the
constraint, UNLESS locking of the tuples is done correctly.

These contraints are not trivial to implement (unfortunally). It would
be great if they where.

- Joris

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] sql formatter/beautifier

[Joris Dobbelsteen]

PostGreSQL (7.4 and onward) has such a thing build-in, but its not
particulary good (simple case works, but once it gets complex it makes a
mess out of it).

For example the postgresql formatted version
$CREATE OR REPLACE VIEW "Subquery" AS 
$ SELECT t1.a, t2.b
$   FROM ( SELECT 1 AS a, 2 AS x) t1
$   JOIN ((( SELECT 1 AS b, 2 AS x
$UNION ALL 
$ SELECT 1 AS b, 2 AS x)
$UNION 
$ SELECT 1 AS b, 2 AS x)
$UNION ALL 
$ SELECT 1 AS b, 2 AS x) t2 ON t1.x = t2.x
$  WHERE (t1.a IN ( SELECT 1
$UNION 
$SELECT 2));
(hopefully the mailer doesn't mess with the spacing)

I just found http://psti.equinoxbase.com/
There is a online demo and it works perfectly on this query:
$CREATE OR REPLACE VIEW "Subquery" AS 
$SELECT t1.a, t2.b
$FROM ( SELECT 1 AS a, 2 AS x) t1
$JOIN ((( SELECT 1 AS b, 2 AS x
$ UNION ALL 
$ SELECT 1 AS b, 2 AS x)
$   UNION 
$   SELECT 1 AS b, 2 AS x)
$  UNION ALL 
$  SELECT 1 AS b, 2 AS x) t2 ON t1.x = t2.x
$WHERE (t1.a IN ( SELECT 1
$ UNION 
$ SELECT 2));

Probably such a thing should be integrated into postgresql...

- Joris

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure
>Sent: vrijdag 2 februari 2007 16:38
>To: postgres general
>Subject: [GENERAL] sql formatter/beautifier
>
>I am wondering if anybody can suggest a good sql formatting 
>tool.  I searched for the obvious things on google and can't 
>find a good solution. The requirements are:
>
>* linux version strongly preffered
>* pl/pgsql preffered, but not required
>* must be able to be run from script (bash, etc)
>* must be highly configurable
>
>free/open source preferred, but not required
>
>merlin
>
>---(end of 
>broadcast)---
>TIP 1: if posting/reading through Usenet, please send an appropriate
>   subscribe-nomail command to [EMAIL PROTECTED] so 
>that your
>   message can get through to the mailing list cleanly
>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] SELECT FOR UPDATE with ORDER BY to avoid row-level deadlock?

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] On Behalf Of Panther
>Sent: dinsdag 30 januari 2007 7:07
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] SELECT FOR UPDATE with ORDER BY to avoid
>row-level deadlock?
>
>Hi,
>
>My problem is that if I try to update more than one row in a table like
>> UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
>in two concurrent transactions, it can result in a deadlock if
>the two UPDATEs visit the rows in a different order.
>The same applies, if I try to
>> SELECT * FROM mytable WHERE not_unique_col = 41 FOR UPDATE;
>
>But what if I try like
>> SELECT * FROM mytable
>> WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
>and do the UPDATE after this? It should never lead to a deadlock,
>assuming the rows selected FOR UPDATE are locked in the order as
>they are returned.
>But is that true? Are the rows selected FOR UPDATE locked in the same
>order as they are returned (as specified in ORDER BY)?
>
>I'm not quite sure (though I tested it on a small table and it looked
>fine), because I (or should I say Google) could not find even one page
>on postgresql.org where this row-level deadlock situation had been
>solved... I could only find Tom Lane's post, where he admitted
>that this
>can lead to a deadlock:
>http://archives.postgresql.org/pgsql-general/2004-11/msg01372.php
>I don't believe that no one thought of this solution before, so there
>must be something wrong with it... :)

I'm wondering: when is the lock actually acquired?
My guess would be (just) before its actually accessed, not after the
result was fetched.

It would probably depend on the selection criteria. Since it could be
that the planner takes the primary_key index scan on some cases, and a
non_unique index scan (or table scan with sort) in other cases.
That's what I would do, choose the index for not_unique_col and sort.
If that's not possible, use the primary key index.
Perhaps the query optimizer is a little bit more clever.

Wouldn't it be safer to disable index usage instead, forcing table
scans? That seems easier to force the database software to use the same
ordering within a query.

You cannot prove that this will always work...
However it MIGHT help in practice, but you cannot be sure... 

- Joris

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Unauthorized users can see db schema and read functions

[Joris Dobbelsteen]

 



  _  

From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Willy-Bas Loos
Sent: dinsdag 30 januari 2007 9:41
To: pgsql-general@postgresql.org
Subject: [GENERAL] Unauthorized users can see db schema and read
functions


Hi,

I've noticed that any user who can logon to a db cluster can read the
schema of all databases in it, including the code of all plpgsql
functions. Even in schema's he/she doesn't have access to. For tables it
just says 'access denied for schema bla', after which the structure is
still shown to the user. For functions, there is no warning at all, you
can just read (copy, paste) away. 
I use pgAdmin3 1.6.2 as a front-end for both linux and windows servers,
but I don't think restricting schema information should be a front-end
responsibility.

o  Why is schema information not restricted? 
 

This is a limitation of <= 8.1.x.
In 8.2 the "CONNECT" priviledge was introducted on the database (still
wondering why it was not done earlier).
 
Probably something similar should be done on the schema's too ;)

 
o  Is there any way to prevent this, other than starting another cluster
for this user's database
 

*   Upgrade to 8.2...
*   (Not sure and seems to contradict with your statement) you can
move everything into a different schema, since public will always be
readable, and others can be restricted

People who know it for sure should correct me, of course.
I found the behaviour strange too...
 
- Joris

Re: [GENERAL] counting query

[Joris Dobbelsteen]

>-Original Message-
>From: Douglas McNaught [mailto:[EMAIL PROTECTED] 
>Sent: zondag 28 januari 2007 16:29
>To: Joris Dobbelsteen
>Cc: John Meyer; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] counting query
>
>"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes:
>
>> What would have been better without surrogate keys all-over:
>> * Easier to write complex queries with much fewer tables to 
>be queried.
>> * Much faster query performance, as fewer tables need to be 
>referenced.
>> * Better integrity enforcement with simple foreign key constraints.
>
>Not this debeta again.  ;)
>
>Surrugate vs natural keys shouldn't make a difference in how 
>many yables you have--they depends on the degree of 
>normalization.  Sounds like you denormalized your database and 
>happened to eliminate surrogate keys at the same time.  Using 
>that to say "surrogate keys are bad" is kind of misleading.

I have perhaps formulated it quite extreme. It was not intended to take
such a extreme stance. I appologize if it will lead to such a
discussion. I'll try to do it a bit more careful next time.

My point is only, be careful with surrogate keys and try not the use
them for everything. In my sole opinion I see them getting used too
much. But then again, what is good and wrong will always be subjective.

So perhaps What would have been better without surrogate keys
all-over should have been "My database where I extremely overdid
it with surrogate keys".

Lets leave it to this.

- Joris

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] counting query

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of John Meyer
>Sent: zondag 28 januari 2007 15:36
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] counting query
>
>Joris Dobbelsteen wrote:
>>>
>>> CREATE TABLE attendance
>>> (
>>>  attendanceid serial primary key,
>> 
>> Why you have this??? You already have (entered,timeperiod,studentid) 
>> that you can use, since that must be unique too. Try to avoid 
>> surrogate keys as much as possible (it really increases performance 
>> and ease-of-writing for complex queries!
>
>
>Correct me if I am wrong, but wouldn't a simple number be a 
>lot easier to look up than a composite key?

No, it is not.
Better, it is, It might be, until you go just a bit larger.

I've learned it when I've build a production database (that's still used
in production and still performs excellent. At the time I was only 15 or
16 or 17 years old. That was 7 to 10 years ago. Since then I've learned
a lot.)

The trouble is, the database consists of well over 40 tables (with
nearly surrogate keys) and joining a bit of data on the far ends of the
database requires you to join arround 10 to 15 tables. If you are doing
something complex you will get lost at some point and really need
graphical tools to just grasp what you are trying to query.
So a bit of a complex query easily results in a excessive number of
tables that must be queried.

Why I did this. At this time I was not aware that you could build a
primary key consisting of multiple columns. And if you look arround you
at the Internet you see (nearly) all databases of free 'web
applications' making excessive use of surrogate keys.
This resulted in a lot of excessive surrogate keys that could have been
easily avoided and where not a required atrifact of the inconsistent
data I had to import.

What would have been better without surrogate keys all-over:
* Easier to write complex queries with much fewer tables to be queried.
* Much faster query performance, as fewer tables need to be referenced.
* Better integrity enforcement with simple foreign key constraints.

If fact, in (guessed) 50% of the queries I could have avoided at least 2
table joins!

Think big. That is why...

- Joris Dobbelsteen

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] counting query

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of garry 
>saddington
>Sent: zondag 28 januari 2007 14:06
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] counting query
>
>I have a table definition such as:
>
>CREATE TABLE attendance
>(
>  attendanceid serial primary key,

Why you have this??? You already have (entered,timeperiod,studentid)
that you can use, since that must be unique too. Try to avoid surrogate
keys as much as possible (it really increases performance and
ease-of-writing for complex queries!

>  entered date DEFAULT current_date NOT NULL,
>  absent boolean,
>  authorization text default 'N',
>  timeperiod char(2) check(timeperiod in('AM','PM')),
>  days varchar(10),
>  studentid int,
>  unique(entered,timeperiod,studentid)
>)

Guessing the meaning a bit, not too self-explaining.

>Which is used to record school attendance data. I am now 
>trying to write a query to identify trends in absences by 
>counting the days column and returning any student that has 
>repeated absences on certain days. I am struggling to return 
>anything that does not need further manipulation in Python 
>before being useful.
>Does anyone have any ideas?

Yes, before starting you must have a well-defined idea on what you want
to know. What should the result look like? In most situations start
simple things, and eventually combine these to something more complex,
but always know what you are querying. You should have some idea of what
results you will be getting.
Of course, this is a proces of discovery rather than following set
rules. Some background on probabilities and statistics really helps.
Personally I like visualization quite a lot to help me with this.

Personally I've found nothing that will beat Excel for doing data
analysis. Learn to use the pivot table and pivot charts. They are
extremely powerful.
However, it can be a bit tricky to transform the input into something
the tool can use. A good starting point is to split the dates into
seperate year, month, day, week values.

Some idea's that might work to get it started:
* A graph with days vs occurrences (count).
* Graph of total sick days per student vs occurrences.
* Graph of Check the occurrences per month/day/week

Next try filtering of the data to form sequential periods and more funny
things.

I believe all this can be done with plain SQL and you don't need any
python or plsql or other languages.

- Joris Dobbelsteen

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] triggers vs b-tree

[Joris Dobbelsteen]

I believe you should design it in a slightly different way:

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>gustavo halperin
>Sent: donderdag 25 januari 2007 21:34
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] triggers vs b-tree
>
>Hello I have a design question:
>
>  I have a table representing Families, and a table 
>representing Persons.
>
> So my question is: If most of the time I need to find all the 
>persons for one asked family what is the best way to do that?
> I think about two methods:
> 1-  Making a b-tree index in ficha_person with the 
>rows family_id and person_id.
> 2 - Adding an array in the table ficha_family 
>containing the persons of this family. And creating a Trigger 
>that update this array for each  person insert/delete in the 
>table ficha_family.

It seems you are quite new (or unfamiliar) to databases.
Are you familiar with "Database Normalization"?

Read this:
http://databases.about.com/od/specificproducts/a/normalization.htm
(Handles all you need)
http://www.phlonx.com/resources/nf3/
(Gives better examples up to 3th normal form)
http://en.wikipedia.org/wiki/Database_normalization
(Wikipedia does not seem to be too good, but it works)

After reading this carefully you should know what option you should
take. The answer is right in there, but there is a lot more useful
information too.

- Joris

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Duplicate key violation

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Brian Wipf
>Sent: donderdag 25 januari 2007 22:42
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] Duplicate key violation
>
>I got a duplicate key violation when the following query was performed:
>
>INSERT INTO category_product_visible (category_id, product_id)
>   SELECT  cp.category_id, cp.product_id
>   FROMcategory_product cp
>   WHERE   cp.product_id = $1 AND
>   not exists (
>   select  'x'
>   fromcategory_product_visible cpv
>   where   cpv.product_id = 
>cp.product_id and
>   cpv.category_id = cp.category_id
>   );
>
>This is despite the fact the insert is written to only insert 
>rows that do not already exist. The second time the same query 
>was run it went through okay. This makes me think there is 
>some kind of race condition, which I didn't think was possible 
>with PostgreSQL's MVCC implementation. I'm unable to duplicate 
>the problem now and the error only occurred once in weeks of 
>use. This is on PostgreSQL 8.2.1 running on openSUSE Linux 
>10.2. Slony-I 1.2.6 is being used for replication to a single 
>slave database.
>
[snip]

This section is relevant:
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html

In the default isolation level "Read commited" you are protected against
"dirty reads".
You are not protected against "nonrepeatable reads" and "phantom reads".

In fact if you start a transaction now, others are not prevented from
inserting records. This can result in a situation where you did not find
the record, since someone else has just instead it after your
transaction was started.

This is not a race condition, but a side-effect.

- Joris

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [HACKERS] [GENERAL] Autovacuum Improvements

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Gregory Stark
>Sent: maandag 22 januari 2007 19:41
>To: Bruce Momjian
>Cc: Heikki Linnakangas; Russell Smith; Darcy Buskermolen; 
>Simon Riggs; Alvaro Herrera; Matthew T. O'Connor; Pavan 
>Deolasee; Christopher Browne; pgsql-general@postgresql.org; 
>pgsql-hackers@postgresql.org
>Subject: Re: [HACKERS] [GENERAL] Autovacuum Improvements
>
>"Bruce Momjian" <[EMAIL PROTECTED]> writes:
>
>> Yep, agreed on the random I/O issue.  The larger question is if you 
>> have a huge table, do you care to reclaim 3% of the table 
>size, rather 
>> than just vacuum it when it gets to 10% dirty?  I realize the vacuum 
>> is going to take a lot of time, but vacuuming to relaim 3% 
>three times 
>> seems like it is going to be more expensive than just vacuuming the 
>> 10% once.  And vacuuming to reclaim 1% ten times seems even more 
>> expensive.  The partial vacuum idea is starting to look like 
>a loser to me again.
>
>Well the answer is of course "that depends".
>
>If you maintain the dead space at a steady state averaging 
>1.5% instead of 5% your table is 3.33% smaller on average. If 
>this is a DSS system that will translate into running your 
>queries 3.33% faster. It will take a lot of vacuums before 
>they hurt more than a 3%+ performance drop.

Good, this means a DSS system will mostly do table scans (right?). So
probably you should witness the 'table scan' statistic and rows fetched
aproaching the end of the universe (at least compared to
inserts/updates/deletes)?

>If it's an OLTP system the it's harder to figure. a 3.33% 
>increase in data density will translate to a higher cache hit 
>rate but how much higher depends on a lot of factors. In our 
>experiments we actually got bigger boost in these kinds of 
>situations than the I expected (I expected comparable to the 
>3.33% improvement). So it could be even more than 3.33%. But 
>like said it depends.
>If you already have the whole database cache you won't see any 
>improvement. If you are right on the cusp you could see a huge benefit.

These tables have high insert, update and delete rates, probably a lot
of index scans? I believe the workload on table scans should be (close
to) none.

Are you willing to share some of this measured data? I'm quite
interested in such figures.

>It sounds like you're underestimating the performance drain 
>10% wasted space has. If we found out that one routine was 
>unnecessarily taking 10% of the cpu time it would be an 
>obvious focus of attention. 10% wasted space is going to work 
>out to about 10% of the i/o time.
>
>It also sounds like we're still focused on the performance 
>impact in absolute terms. I'm much more interested in changing 
>the performance characteristics so they're predictable and 
>scalable. It doesn't matter much if your 1kb table is 100% 
>slower than necessary but it does matter if your 1TB table 
>needs 1,000x as much vacuuming as your 1GB table even if it's 
>getting the same update traffic.

Or rather, the vacuuming should pay back.
A nice metric might be: cost_of_not_vacuuming / cost_of_vacuuming.
Obviously, the higher the better.

- Joris Dobbelsteen

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Autovacuum Improvements

[Joris Dobbelsteen]

>-Original Message-
>From: Alvaro Herrera [mailto:[EMAIL PROTECTED] 
>Sent: dinsdag 9 januari 2007 22:18
>To: Joris Dobbelsteen
>Cc: Chris Browne; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Autovacuum Improvements
>
>Joris Dobbelsteen wrote:
>
>> Now we have at least one different model, lets mix in other captures 
>> and situations. So it cannot be done with only YOUR data, I 
>fully agree.
>> But if you have sufficient data you can find the 
>generalization of the 
>> model to make it work (resonable) in sufficient situations.
>> Of course models need time to evolve, but so does the implementation 
>> currently at a slow rate. From do it yourself, to scripts, to the 
>> current autovacuum integration (which is good). From doing 
>all tables 
>> sequentially to having some intelligence by update 
>thresholds, to what 
>> will be next.
>> 
>> I think you should better solve the problem is this ways, as models 
>> are relative easy to compare compared to arguments without 
>> analyzable/simulatible data.
>
>To be frank, I'm not sure I understand what you're saying 
>here.  I'm sure more analysis is good; that's easy to agree with.

I hope to make it more clear.

With a model I mean something which I can use to:
* estimates the performance effects of 'defragmentation', excessive
pages or a low page fill factor.
* estimates how much vacuum affects the performance while it is running.
* as a result, estimate how the performance was improved as a result of
vacuuming.

With this its possible to guess how much vacuum costs and if you will
benefit from vacuuming. And hopefully it gives more details on how
performance is affected.

Examples (under DISCLAIMER, its my low-end machine):
* How does fragmentation affect inserts. I've observed that they take
constant time, regardless of the table size, transaction size or number
of columns. On my low-end system it was limited by the CPU. So here it
does not require vacuuming. Probably some test elsewhere will give
different results.
* How does it affect index scans? (I expect it mainly related to index
size and cache limits)
* How does it affect table scans (I expect it goes linear with the table
size).
* How does it affect updates?
* How does it affect deletes?
* (What did I forget?)
* Vacuum seems to scale linear with the table size. Futher its extremely
I/O intensive and not much else.

Do they depend on table size (like vacuum/table scan), on index size
(index scans?) or do they scale O(1) (inserts?)...

I've not yet found anything concrete, except several comments that it
will depend on your situation. However, the information should be
available or is mainly estimated by experience of DBAs.

>However, I don't want to be trapped in a design that's too 
>hard to implement, or too hard for DBAs to manage.

Exactly...
If you can model it, you can implement it.

But you can also analyze it ahead of time:
* You can see what you need to configure, as its in the model. Perhaps
much can be done to automatically tune
* Furthermore you can predict its impact (expected performance) well
ahead of implementation and deployment.
* You can test your ideas on a model and see if it would work out, of
course leaving you to see how accurate they are in practical
situations...

That is why I proposed it.

>There have been proposals to add these knobs:
>- maximum number of simultaneous processes (and make it more than 1)
Indeed, vacuum takes to long on some tables. My finding is that the
vacuum time scales linearly with the number of pages. Perhaps vacuum can
be more efficient or do partial cleanups instead of full page scans?
>- times of day on which to change parameters (i.e. disable vacuum
>  altogether, or make it more agressive or less)

- Something DBAs call "HOT" tables.
- There is something about maintenance windows, or would you rather let
the administrator specify when the database has it business hours.

There has been discussion about heuristics and metrics required for
that. In some part of the thread its called "dirtyness" which has a
quite ambigious meaning.

- Joris

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Autovacuum Improvements

[Joris Dobbelsteen]

>-Original Message-
>From: Alvaro Herrera [mailto:[EMAIL PROTECTED] 
>Sent: dinsdag 9 januari 2007 3:43
>To: Joris Dobbelsteen
>Cc: Chris Browne; pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Autovacuum Improvements
>
>Joris Dobbelsteen wrote:
>> Why not collect some information from live databases and 
>perform some 
>> analysis on it?
>
>We already capture and utilize operational data from 
>databases: 
[snip]

What I was point out is that you have collected the metrics required to
establish a maintenance policy (for vacuum in this case). This mean
others can collect it too.

>But that data alone is not sufficient.  You (the DBA) have to 
>provide the system with timing information (e.g., at what time 
>is it appropriate to vacuum huge tables).
[snip]

That would be your expected result of what you want to happen.
So there are a few more metrics that the DBA has to set.

What is wrong with building a model and performing some analysis on this
model. If it suits you situation it that would be a starting point.

>Capturing data about someone's database is not going to help 
>someone else's vacuuming strategy, because their usage 
>patterns are potentially so different; and there are as many 
>usage patterns as Imelda Marcos had shoes (well, maybe not 
>that many), so any strategy that considers only two particular 
>pairs of shows is not going to fly.  We need to provide enough 
>configurability to allow DBAs to make the vacuumer fit their situation.

Now we have at least one different model, lets mix in other captures and
situations. So it cannot be done with only YOUR data, I fully agree.
But if you have sufficient data you can find the generalization of the
model to make it work (resonable) in sufficient situations.
Of course models need time to evolve, but so does the implementation
currently at a slow rate. From do it yourself, to scripts, to the
current autovacuum integration (which is good). From doing all tables
sequentially to having some intelligence by update thresholds, to what
will be next.

I think you should better solve the problem is this ways, as models are
relative easy to compare compared to arguments without
analyzable/simulatible data.

- Joris

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Autovacuum Improvements

[Joris Dobbelsteen]

Why not collect some information from live databases and perform some
analysis on it?

Possible values required for (to be defined) vacuum heuristic,
Human classification of tables,
Updates/Transactions done (per table/db),
Growth of tables and indexes,
(all with respect to time I believe)

Collecting real data has worked for me in the past way better than
arguing about potential situations. Now you can let you model go wild
and see if it does what you thing should happen (obviously comparing
with the current autovacuum implementation).

What statistics would you need?
Who is capable & willing of capturing it?
Who is willing to do some analysis...

Just my EUR 0.02...

- Joris

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Chris Browne
>Sent: maandag 8 januari 2007 22:30
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Autovacuum Improvements
>
>[EMAIL PROTECTED] (Csaba Nagy) writes:
>> On Sun, 2006-12-24 at 03:03, Christopher Browne wrote:
>> [snip]
>>> Seems to me that you could get ~80% of the way by having 
>the simplest
>>> "2 queue" implementation, where tables with size < some 
>threshold get 
>>> thrown at the "little table" queue, and tables above that 
>size go to 
>>> the "big table" queue.
>>
>> That would most definitely not cut it for me, I have more than 2 
>> categories of tables:
>>
>>  - a few small but very often updated/inserted/deleted table: these 
>> must be continuously vacuumed, your "little queue" is not 
>good enough 
>> for that, as even the round trip between the small tables could lead 
>> to bloat on them;
>
>I disagree; if we added more "work processes," that could eat 
>quickly through the short end of the queue.
>
>>  - a few small and moderately updated, that could live with the 
>> "little queue";
>>
>>  - a few big and frequently updated, but which only have a small 
>> percentage of rows actively updated at any time: those could 
>live with 
>> the big queue;
>>
>>  - the rest which are rarely updated, I would put those in a 
>separate 
>> queue so they won't affect the rest, cause vacuuming them is really 
>> mostly not critical;
>>
>> The point is that I'm not sure there couldn't be even more 
>reasons to 
>> split the tables in even more queues based on the importance of 
>> vacuuming them combined with update rate and their size. If 
>I can set 
>> up my own queues I can experiment with what works best for me... for 
>> the base setup you could set up some default queues. I wonder though 
>> how would you handle dynamics of tables, I mean when will a small 
>> table which grows start to be considered a big table for the purpose 
>> of putting it in one queue or the other ? I guess it would 
>be done on 
>> analyzing the table, which is also handled by autovacuum, so tables 
>> with no vacuum queue settings could go to one of the 2 
>default queues 
>> you mention.
>
>The heuristic I was thinking of didn't involve having two 
>queues, but rather just 1.  By having some size information, 
>work processes could eat at the queue from both ends.
>
>If you have cases where tables need to be vacuumed *really* 
>frequently, then you make sure that they are being injected 
>frequently, and that some of the workers are tied to Just 
>Doing Small Tables.
>
>I think that *does* cover your scenario quite adequately, and 
>without having to get into having a bunch of queues.
>
>The heuristic is incomplete in one other fashion, namely that 
>it doesn't guarantee that tables in the middle will ever get 
>"gotten to."
>That mandates having a third policy, namely to have a worker 
>that goes through tables in the (singular) queue some form of 
>chronological order.
>
>>> That should keep any small tables from getting "vacuum-starved."
>>> 
>>> I'd think the next step would be to increase the number of queues, 
>>> perhaps in a time-based fashion.  There might be times when it's 
>>> acceptable to vacuum 5 tables at once, so you burn thru 
>little tables 
>>> "like the blazes," and handle larger ones fairly promptly.  
>And other 
>>> times when you don't want to do *any* big tables, and limit 
>a single 
>>> queue to just the itty bitty ones.
>>
>> This is all nice and it would be cool if you could set it up per 
>> vacuum queue. I mean how much more effort would be to allow vacuum 
>> queues with generic settings like time windows with max number of 
>> threads for each window, and let the user explicitly assign 
>tables to 
>> those queues, instead of hard coding the queues and their 
>settings and 
>> assign tables to them based on size or any other heuristics ?
>>
>> For the average application which needs simple settings, there could 
>> be a default setup with the 2 queues you mention. If it would be 
>> possible to set up some rules to assign tables to queues based on 
>> their properties on analyze time, instead of explicitly assigning to 
>> one queue or other, that would be nice too, and then you can 
>> completely c

Re: [GENERAL] select union with table name

[Joris Dobbelsteen]

Try:
select blue.name, 'blue' from blue union select red.name, 'red' from
red;

Not tested, but that should work.
One thing to remember:
If blabla is in both blue and red, it will appear twice, instead of only
once as in your example. 

- Joris

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Akbar
>Sent: donderdag 28 december 2006 13:10
>To: pgsql-general@postgresql.org
>Subject: [GENERAL] select union with table name
>
>Hi, I have two tables.
>create table blue (
>  id serial primary key,
>  name text not null,
>  kill text not null
>);
>
>create table red (
>  id serial primary key,
>  name text not null,
>  kiss text not null
>);
>
>select blue.name from blue union select red.name from red; 
>give me this:
>name
>'blabla'
>'bubu'
>'haha'
>'kkk'
>
>I want this:
>nametable_name
>'blabla'   blue
>'bubu'blue
>'haha'red
>'kkk'  red
>
>Could I?
>
>---(end of 
>broadcast)---
>TIP 3: Have you checked our extensive FAQ?
>
>   http://www.postgresql.org/docs/faq
>

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Autovacuum Improvements

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of 
>Matthew O'Connor
>Sent: woensdag 20 december 2006 2:53
>To: Glen Parker; Postgres General
>Subject: Re: [GENERAL] Autovacuum Improvements
>
>Alvaro Herrera wrote:
[snip]
>I would go one step further and suggest that when the 
>maintenance window system gets completed we give it a default 
>setup of midnight to 6AM or something like that.
>

Do not do that...
For this reason:
* Any serious database administrator will read the documentation and
take the time to configure the system for such a setup. The setup will
be specific to his/her demands, competing with the backup window
perhaps.
* Any new user, developers and hobbiests don't need such things to
increase performance a little and several most likely turn off their
systems during the night. This effectively results in people that get
serious problems using postgresql, because vacuum never runs. You know
who they will blame for it.

Just my opinion...

- Joris

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] CONNECT privilege

[Joris Dobbelsteen]

I've set up a little test to find out how much permissions users have in
PostGreSQL.
It seems that the CONNECT privilege cannot be assigned or is not
recognized by postgresql 8.1.

When using pgAdmin-III it does not display the granted CONNECT
priviledge.
Also when doing GRANT CONNECT FOR DATABASE  TO  it
succeeds, but I fail to observe its effects: i.e. users are not allowed
to a database.

The public rule has been removed of course from the database.

I did find GRANT CONNECT in the documentation, but no notes regarding
its operation.
Also I have read at least the pgAdmin documentation advices to use the
connect privilege rather than pg_hba configuration file, which seems
logical and easier to manage.

Any way I seem to be doing something wrong or missed something?

Thanks,

- Joris