Re: [GENERAL] Expected accuracy of planner statistics
On Sep 28, 2006, at 8:51 PM, Tom Lane wrote: [..] The information we've seen says that the only statistically reliable way to arrive at an accurate n_distinct estimate is to examine most of the table :-(. Which seems infeasible for extremely large tables, which is exactly where the problem is worst. Marginal increases in the sample size seem unlikely to help much ... as indeed your experiment shows. I think a first step might be to introduce a new analyze command, such as ANALYZE FULL. This would be executed deliberately (IOW not by autovacuum) like CLUSTER or VACUUM FULL when deemed necessary by the dba. The command as implied would scan the entire table and fill in the stats based on that (as analyze used to do IIRC). It would also be useful if this command froze the stats so that autovacuum didn't clobber them with inaccurate ones shortly thereafter. Perhaps an explicit ANALYZE FULL FREEZE command would be useful for that case, the behavior being that a normal ANALYZE would not overwrite the stats for a stats-frozen table, another ANALYZE FULL would, however. Such a frozen state would also be useful if you wanted to hand-tweak stats for a single table and have it stick and still use autovac. As I understand it now, with autovac on, you cannot do that unless you hack the pg_autovacuum table (i.e., set anl_base_thresh to an artificially high value). Another option (that I think others have suggested) would be to make this the behavior for VACUUM ANALYZE. That saves the baggage of a new command at least. Another advantage would be that the autovac daemon could run it. Perhaps some smarts could also be built in. What if VACUUM ANALYZE first runs a normal (sampled) ANALYZE. Then it performs the VACUUM with full ANALYZE pass. The stats gathered by the latter full pass are compared to that of the first sampled pass. If the full ANALYZE statistics are sufficiently different from the sampled pass, then the table is flagged so that normal ANALYZE is not performed by the autovac daemon on that table. Also, a global ANALYZE could ignore it (though this seems more magical). A more pie-in-the-sky idea could take advantage of the fact that the larger a table is the less likely the statistics will change much over time. If we cannot afford to sample many rows in a given analyze pass, then perhaps we should use a newton's method approach where we attempt to converge on an accurate value over time with each analyze pass contributing more samples to the statistics and honing them incrementally rather than simply replacing the old ones. I'm not statistician, so it's not clear to me how much more state you would need to keep between analyze passes to make this viable, but in order for this to work the following would need to be true: 1) Analyze would need to be run on a regular basis (luckily we have autovaccum to help). You would want to analyze this table periodically even if nothing much changed, however. Perhaps tuning the autovac parameters is enough here. 2) Each analyze pass would need to sample randomly so that multiple passes tend to sample different rows. 3) The stats would need to somehow be cumulative. Perhaps this means storing sample values between passes, or some other statistical voodoo. 4) Needs to be smart enough to realize when a table has changed drastically, and toss out the old stats in this case. Either that or we require a human to tell us via ANALYZE FULL/VACUUM ANALYZE. I think that the incremental stats approach would more or less depend on the full ANALYZE functionality for bootstrapping. I think when you first load the table, you want to get the stats right immediately and not wait some indeterminate amount of time for them to converge on the right value. -Casey ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication and PITR
Jeff Davis wrote: If it's a statement queue, what happens when you do INSERT ... VALUES (random())? Can the statements be executed out of order on the slave or are they serialized? That is very relevant, and my ref to MySQL replication was only the relatively ease of its setup. And in most of the situation it works OK, but it has its limits and is not to be trusted 100% (I had to make special test records to see if the queue was stock). I Just imagined PITR data used instead of SQL update queues, for replication. The updates are queued on the master and transferred over the network to the slave. You don't need to do any nfs tricks. Ok, nice ... as long as Slony don't write local files there is no problem. I really have to start reading about Slony, to understand it better ... I may get surprised :-) Slony is designed to improve read performance. If you want better write performance pretty much all you can do is use a better I/O system or partition the data so that all the data is not stored on every server. Classic for databases :-) Often, read queries are the bottleneck, so that's why so many people find replication like slony useful. Yes and that goes for me too. /BL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Replication and PITR
Jim Nasby wrote: You can work around it right now, too; you just need an external process that will find the active WAL file and periodically copy it to the backup. I'm pretty sure there's info in the archives about the details of setting this up, and there's also the PITRHA project on pgFoundry. I have seen the PITRHA project, but it looked a bit to much like a hack to me. By doing to much hacking I just end up locking me into a fixed PG version, and that I don't like the sound of. /BL ---(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] Replication and PITR
Andrew Sullivan wrote: Note that, the last time I looked at it, there was no interlock to ensure that your statement queue (which is basically just a log of statements as executed on the master) was not accidentally blown away by your cleanup process before your target replicas were up to date. This might have improved recently, but when I looked at it MySQL's async replication was high on the ease of use and low on the works in sticky situations. As I say, they may have fixed it; but I advise people to look very carefully at how it works before deciding it is adequate. I know the MySQL scheme is not perfect, but the setup of one is relatively easy, but you still have to know what is going on, otherwise you are not going to get a good night sleep :-) The important thing to remember about database replicas is that you're _already_ planning for the small percentage of cases where things break. Therefore, an 80/20 solution is not good enough: the thing has to work when most things have broken, or it's no use to you. I agree, and that is why you have to be very careful about your choice :-) Well the nice thing about using a slave DB for reporting is the focus to keep it in sync. If it is a backup server you may ignore it for a while, and then Murphy strikes at you :-) No. I suggest you have a look at the docs, and take these questions to the (again functioning) Slony list, where people can advise about that. Thanks, I willl ! /BL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replication and PITR
Robert Treat wrote: Hmm almost sounds like what you really want is mammoth replicator... lower level than slony, built into the db, can handle ddl (iirc) not oss though. Yes, that may be true but I think I will try out Slony first, as the design of the DB (DDL) is quite static. I dislike getting locked up to some non OSS version at the moment. But thanks for the advise. /BL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Full Text fuzzy search
Thanks for the replies, And all the functions work with levenstein and soundex? Can they also return the percentage of similarity? Regards michael ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Dead Lock problem with 8.1.3
Tom Lane wrote: I bet the problem is that you're not getting a hashed subplan in 8.1. What do you have work_mem set to on the two systems? The fact that the rowcount estimate for the subplan is much larger may be causing the change, too. Why is that --- perhaps you haven't ANALYZEd stud_vera recently on one system or the other? How many rows will really come out of the sub-select (ie, what's select count(*) from stud_vera where veraid = 2)? HEUREKA... Increasing the work_mem had solved the problem. Result: 3170 rows in 1,487.927ms. Working_mem was set to the default of 1MB. Increasing it to 4 solved it. Are there any suggestions for the size. Maybe depending on database size? Many thanks! But I still wonder why it didn't work with the lower size of working_mem and the temporary file on the disk? I had a similar problem in the past where I forget to ANALYZE and before ANALYZEing also a temporary file was used. The time difference had been from ten seconds to five minutes and not from one second to over 40 hours. The temporary file is created and has a size of around 2MB and doesn't change size while working. I fear to run into the same problem, if I use more complex queries. And, the problem with the estimated rows is still very interesting. The Table is FULLy ANALYZEd and the actual count of stud_vera with verid=2 is 49176, so the 53000 are real close. Don't now, why it is so low on 8.0.8? One other interesting thing is that the query plan has changed after increasing the working mem: Hash Join (cost=12991.28..21472.83 rows=7512 width=4) Hash Cond: (outer.sid = inner.sid) - Bitmap Heap Scan on stud_vera v (cost=1841.02..5834.80 rows=15023 width=4) Recheck Cond: (veraid = 34) - Bitmap Index Scan on stud_vera_sid_veraid_idx (cost=0.00..1841.02 rows=15023 width=0) Index Cond: (veraid = 34) - Hash (cost=11008.74..11008.74 rows=56607 width=4) - Seq Scan on stud s (cost=7617.57..11008.74 rows=56607 width=4) Filter: (NOT (hashed subplan)) SubPlan - Seq Scan on stud_vera (cost=0.00..7488.20 rows=51747 width=4) Filter: (veraid = 2) *greets* Kai ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 8.1.4 compile problem
Hi all, i am compiling postgresql 8.1.4 on AMB x86_64 platform. configure runs fine but shows output (snippet shown) as follows: ... checking for int8... no checking for uint8... no checking for int64... no checking for uint64... no ... my gcc -v gives me: Reading specs from /usr/lib/gcc/x86_64-redhat-linux/3.4.4/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=x86_64-redhat-linux Thread model: posix gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) how can i add int8,uint8,int64 and uint64 functionality into postgres ? are there any special flags that i am missing ? tia, regards, KM ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 8.1.4 compile problem
Hi, On Friday 29 September 2006 11:01, km wrote: | i am compiling postgresql 8.1.4 on AMB x86_64 platform. | configure runs fine but shows output (snippet shown) as follows: | ... | checking for int8... no | checking for uint8... no | checking for int64... no | checking for uint64... no I'd say, this is expected output from configure. Configure is just a mechanism to help constructing an abstraction for different types of OS (i.e. things like can we use this C type? Do we need to use another type instead?). [...] | how can i add int8,uint8,int64 and uint64 functionality into postgres ? are | there any special flags that i am missing ? The smallint, integer and bigint types are available regardless of that output from configure. Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.1.4 compile problem
On Fri, Sep 29, 2006 at 11:48:09AM +0200, Thomas Pundt wrote: I'd say, this is expected output from configure. Configure is just a mechanism to help constructing an abstraction for different types of OS (i.e. things like can we use this C type? Do we need to use another type instead?). [...] | how can i add int8,uint8,int64 and uint64 functionality into postgres ? are | there any special flags that i am missing ? The smallint, integer and bigint types are available regardless of that output from configure. I was in a doubt if int64,int8,uint64 and uint8 are not supported after setting up the db. thanks for clarifying my doubt. will proceed with gmake :) regards, KM -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PostgreSQL 8.1.4 on Vista RC1
I receiveWindows VistaRC1 for testing, so I try to install PG 8.1.4.The installer fails : - On the creation of the user Postgres, - On the creation of the service. I found workaround for that : - I create manually the Postgres user with a password, - I install PG 8.1.4 WITHOUT creating the service. So I start manually PG and it work fine ! On an XP Workstation hosting a PG 8.1.4 server I run RegEdit and I export the key : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.1 in a file. On the Vista RC1 WorkStation I import this .reg file, I correct the files locationand I restart the WorkStation. In the Control Panel I open the services MMC and I test this service : Ok. Now I'm testing PG 8.1.4and PgAdim 1.6 Béta 1 with my own application... No problem for this moment ... It would be nice to adapt the installer... Best regards. Luc Rolland
Re: [GENERAL] PostgreSQL 8.1.4 on Vista RC1
I receive Windows Vista RC1 for testing, so I try to install PG 8.1.4.The installer fails : - On the creation of the user Postgres, - On the creation of the service. I found workaround for that : - I create manually the Postgres user with a password, - I install PG 8.1.4 WITHOUT creating the service. So I start manually PG and it work fine ! On an XP Workstation hosting a PG 8.1.4 server I run RegEdit and I export the key : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.1 in a file. On the Vista RC1 WorkStation I import this .reg file, I correct the files location and I restart the WorkStation. In the Control Panel I open the services MMC and I test this service : Ok. Now I'm testing PG 8.1.4 and PgAdim 1.6 Béta 1 with my own application... No problem for this moment ... It would be nice to adapt the installer... This is a known issue in the installer, that's currently being worked on. See http://pgfoundry.org/projects/pginstaller, there is a tracker for the issue. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Expected accuracy of planner statistics
In article [EMAIL PROTECTED], [EMAIL PROTECTED] (Jim C. Nasby) wrote: The problem is that you can't actually get a good n_distinct estimate if you're sampling less than a very large chunk of the table. Since our sampling maxes out at something like 30k pages, at some point the n_distinct estimates just degrade. :( Can the DBA just set n_distinct? Sometimes s/he just knows what the value should be. Then, of course, the questions becomes how to keep vacuum et al from messing it up. -arturo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Expected accuracy of planner statistics
Arturo Perez [EMAIL PROTECTED] writes: Can the DBA just set n_distinct? Sometimes s/he just knows what the value should be. Having an expensive process run once in a while and setting this value also sounds interesting. If it has to be calculated every time then this is a bad thing, but having some kind of command or function to update it that could be called when the database has a lower load would be interesting. For companies that work from 8am to 5pm this could be scheduled to run every night... Then, of course, the questions becomes how to keep vacuum et al from messing it up. It could not touch these setting if the specific command isn't called, it could gain a new parameter VACUUM FULL N_DISTINCT ... to touch it (and then we probably discard the extra command / function) or it could update these settings when called with ANALYZE only... -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Expected accuracy of planner statistics
[snip] Having an expensive process run once in a while and setting this value also sounds interesting. [snip] What about externalizing the statistics calculation ? I mean, would it make sense to have for e.g. a WAL-fed standby which has an additional process which keeps the statistics in sync based on the incoming WAL records, and feeds back the stats to the master as soon as they change significantly ? The standby would be able to crunch ALL the data, in almost real time... with almost no overhead for the master. It would require though another server... but I guess where analyze is a problem, throwing another server at it is not a problem. Cheers, Csaba. ---(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] Can i see server SQL commands ?
On 29/9/2006 11:47, Adnan DURSUN [EMAIL PROTECTED] wrote: - Original Message - From: Jim C. Nasby [EMAIL PROTECTED] Didn't someone just ask this yesterday? Yes. i did it :-) I believe you can get that info by increasing client_min_messages. What you specifically have to set it to I don't know; my guess would be log or debug1. I set it that but none of them effect the result :-( I think, answer to this question is no ! Things that i want to see is what SQL commands run by backend while ; * Inserting a row (how checks FKs) * Updating row (how checks FKs and unique constrains) * deleting row (how checks FKs) Best Regards Adnan DURSUN Is using EXPLAIN not sufficient for your needs? I haven't tried to find this info in the log myself, but - client_min_messages and log_min_messages determine when to log, you aren't interested in standard usage log info so you would want to try debug1 through to debug5 and see which gives you the detail you want. The main settings you will want would be debug_print_parse = on debug_print_plan = on This should give you the parser and planner info which you are after. These may also help debug_print_rewritten = on debug_pretty_print = on log_statement = all -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Foreign key constraint delete fire order
Hi!
I wish foreign key constraint trigger guarantees that rows in
referensing table are deleted before the rows in referenced table is
deleted.
Here are one inventory table and two transaction tables serving to
explain the reasons:
CREATE TABLE inv(
warehouse TEXT
,item TEXT
,qty INT2
,PRIMARY KEY(warehouse,item)
);
CREATE TABLE master(
xid INT2 PRIMARY KEY
,warehouse TEXT
);
CREATE TABLE detail(
xid INT2
,item TEXT
,qty INT2
,PRIMARY KEY(xid,item)
,CONSTRAINT fk FOREIGN KEY (xid) REFERENCES master (xid) ON UPDATE
CASCADE ON DELETE CASCADE
);
This rule (or a pair of trigger+function) tries to subtract inventory
automatically when rows are deleted from detail table:
CREATE RULE rd AS ON DELETE TO detail DO
(
UPDATE inv SET qty=qty-OLD.qty WHERE warehouse
=(SELECT warehouse FROM master WHERE xid=OLD.xid)
);
Because we might delete rows from either master or detail, rule rd
is attached to detail table. Problem is that inventory does not decrease
if we delete a row from master table because
SELECT warehouse FROM master WHERE xid=OLD.xid
returns nothing the moment the rule is fired.
With existing fire order of the integrity foreign key constraint, the
implementation of update propagation as shown in this example becomes
very complicate. First, this constraint must not exist:
CONSTRAINT fk FOREIGN KEY (xid) REFERENCES master (xid) ON UPDATE
CASCADE ON DELETE CASCADE
Secondly, triggers along with complicate functions must be created and
attached to master and detail tables.
Does my wish make sense or violate any standard?
Best Regards,
CN
-
db2=# \d detail
Table public.detail
Column | Type | Modifiers
+--+---
xid| smallint | not null
item | text | not null
qty| smallint |
Indexes:
detail_pkey PRIMARY KEY, btree (xid, item)
Foreign-key constraints:
fk FOREIGN KEY (xid) REFERENCES master(xid) ON UPDATE CASCADE ON
DELETE CASCADE
Rules:
rd AS
ON DELETE TO detail DO UPDATE inv SET qty = inv.qty - old.qty
WHERE inv.warehouse = (( SELECT master.warehouse
FROM master
WHERE master.xid = old.xid))
db2=# insert into inv values('w','a',20);
INSERT 0 1
db2=# insert into master values(1,'w');
INSERT 0 1
db2=# insert into detail values(1,'a',5);
INSERT 0 1
db2=# select * from inv;
warehouse | item | qty
---+--+-
w | a| 20
(1 row)
db2=# delete from master;
DELETE 1
db2=# select * from inv;
warehouse | item | qty
---+--+-
w | a| 20
(1 row)
--
http://www.fastmail.fm - And now for something completely different
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] using schema's for data separation
snacktime wrote: This has worked well so far but it's a real pain to manage and as we ramp up I'm not sure it's going to scale that well. So anyways my questions is this. Am I being too paranoid about putting all the data into one set of tables in a common schema? For thousands of clients what would you do? Hard to say what to paranoid really is, so let me ask you what you think won't scale? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] benchmark suite
Hi all, Is there any good benchmark suite for testing postgresql performance? i tried to work with pgbench but found pgbench source (v 8.0.x and 7.4.x) but couldnt compile with gcc ($gcc -o pgbench pgbench.8.0.x.c) postgres 8.1.4 is on AMDx86_64 platform. regards, KM ---(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] benchmark suite
On Fri, Sep 29, 2006 at 07:27:49PM +0530, km wrote: Is there any good benchmark suite for testing postgresql performance? I suggest looking at the excellent software provided by OSDL. http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/ A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(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] using schema's for data separation
On 29/9/2006 15:29, snacktime [EMAIL PROTECTED] wrote: This has worked well so far but it's a real pain to manage and as we ramp up I'm not sure it's going to scale that well. So anyways my questions is this. Am I being too paranoid about putting all the data into one set of tables in a common schema? For thousands of clients what would you do? I would think of having a client table with their id as a permanent part of the where clause so that you can't work without specifying which client you are working for at the time. Not sure if a trigger would be able to ensure you can't add, update or delete unless the clientID is included (pretty sure you don't get to see the SQL only the results). You may need to write a plugin or custom mod to get 100% certainty that a statement can't be run without the clientID included in the search. It would have to be an easier solution than continually updating thousands of schemas to keep them in sync. Thinking about it - it should only be a small source change to the sql parser to stop it from running a statement that didn't include clientID in the where clause. A small change that is easy to add again to new versions as they are released. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] benchmark suite
On Fri, Sep 29, 2006 at 07:27:49PM +0530, km wrote: Is there any good benchmark suite for testing postgresql performance? I suggest looking at the excellent software provided by OSDL. http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/ ya i have tried the dbt1 (database test 1 - which is what i require) from the ODSL site but couldnt compile as i got the error: cache.c: In function `main': cache.c:134: error: `sname2' undeclared (first use in this function) cache.c:134: error: (Each undeclared identifier is reported only once cache.c:134: error: for each function it appears in.) cache.c:146: error: `dbname2' undeclared (first use in this function) cache.c:150: error: `uname2' undeclared (first use in this function) cache.c:154: error: `auth2' undeclared (first use in this function) cache.c: In function `warm_up_cache': cache.c:421: error: storage size of 'dbc' isn't known cache.c:421: warning: unused variable `dbc' cache.c: In function `usage': cache.c:730: error: `uname2' undeclared (first use in this function) cache.c:730: error: `auth2' undeclared (first use in this function) make[1]: *** [cache.so] Error 1 make[1]: Leaving directory `/root/osdl/dbt1-v2.1/cache' make: *** [cache_exe] Error 2 any ideas how to circumvent the problem? regards, KM -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] using schema's for data separation
Shane Ambler [EMAIL PROTECTED] writes: Thinking about it - it should only be a small source change to the sql parser to stop it from running a statement that didn't include clientID in the where clause. A small change that is easy to add again to new versions as they are released. I'd avoid modifying source code and go with either a function or view. All queries should be against those and they'd fail if the id is missing. All filtered tables should have an index on such id, of course... -- Jorge Godoy [EMAIL PROTECTED] ---(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] using schema's for data separation
I am using a similar solution, and I tested it with a test containing 20K+ different schemas. Postgres didn't show slowness at all even after the 20K (over 2 million total tables) were created. So I have feeling it can grow even more. Guy. On 9/28/06, snacktime [EMAIL PROTECTED] wrote: I'm re evaluating a few design choices I made a while back, and one that keeps coming to the forefront is data separation. We store sensitive information for clients. A database for each client isn't really workable, or at least I've never though of a way to make it workable, as we have several thousand clients and the databases all have to be accessed through a limited number of web applications where performance is important and things like persistant connections are a must. I've always been paranoid about a programmer error in an application resulting in data from multiple clients getting mixed together. Right now we create a schema for each client, with each schema having the same tables. The connections to the database are from an unprivileged user, and everything goes through functions that run at the necessary privileges. We us set_search_path to public,user. User data is in schema user and the functions are in the public schema. Every table has a client_id column. This has worked well so far but it's a real pain to manage and as we ramp up I'm not sure it's going to scale that well. So anyways my questions is this. Am I being too paranoid about putting all the data into one set of tables in a common schema? For thousands of clients what would you do? Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Family management on rails: http://www.famundo.com - coming soon! My development related blog: http://devblog.famundo.com ---(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] using schema's for data separation
snacktime wrote: I'm re evaluating a few design choices I made a while back, and one that keeps coming to the forefront is data separation. We store sensitive information for clients. A database for each client isn't really workable, or at least I've never though of a way to make it workable, as we have several thousand clients and the databases all have to be accessed through a limited number of web applications where performance is important and things like persistant connections are a must. I've always been paranoid about a programmer error in an application resulting in data from multiple clients getting mixed together. Right now we create a schema for each client, with each schema having the same tables. The connections to the database are from an unprivileged user, and everything goes through functions that run at the necessary privileges. We us set_search_path to public,user. User data is in schema user and the functions are in the public schema. Every table has a client_id column. This has worked well so far but it's a real pain to manage and as we ramp up I'm not sure it's going to scale that well. So anyways my questions is this. Am I being too paranoid about putting all the data into one set of tables in a common schema? For thousands of clients what would you do? Hi, where I work we have similar issues wherein we have thousands of clients who each have large amounts of the same kind of data that needs to be kept separate. What we've done is to use table inheritance. So, we have a group of base account data tables and whenever a new account is added they get a set of tables that inherit from these base tables. This works well in that whenever we need a global schema change to any of these tables we can just alter that pertinent base table and the change will cascade down to the child tables. In addition, we can customize individual accounts' tables however we may need without worrying about screwing up other accounts' data. -- erik jones [EMAIL PROTECTED] software development emma(r) ---(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] 8.1.4 compile problem
I'd say, this is expected output from configure. Configure is just a mechanism to help constructing an abstraction for different types of OS (i.e. things like can we use this C type? Do we need to use another type instead?). | how can i add int8,uint8,int64 and uint64 functionality into postgres ? are | there any special flags that i am missing ? The smallint, integer and bigint types are available regardless of that output from configure. I was in a doubt if int64,int8,uint64 and uint8 are not supported after setting up the db. thanks for clarifying my doubt. will proceed with gmake :) regards, KM let me add this too: i have compiled postgres 8.1.4 even if configure didnt detect int8,uint8,int64 and uint64. i have tried to create a test table with datattype as int64 , but it says no such datatype, same is with uint8 an uint64 datatypes. of the four mentioned above, int8 only is recognised as a datatype. which means i have compiled postgresql without int64/uint64 support ??? any gotchas ? tia regards, KM ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Expected accuracy of planner statistics
Tom Lane wrote: The information we've seen says that the only statistically reliable way to arrive at an accurate n_distinct estimate is to examine most of the table :-(. IIRC I picked an equation out of the literature partially on the basis of it being simple and fairly cheap to compute... I'm very curious about this - can you recall where you got this, or at least point me to where in the code this happens? Thanks. - John Burger MITRE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.1.4 compile problem
km [EMAIL PROTECTED] writes: i have compiled postgres 8.1.4 even if configure didnt detect int8,uint8,int64 and uint64. i have tried to create a test table with datattype as int64 , but it says no such datatype, same is with uint8 an uint64 datatypes. At the SQL level, these datatypes are named after byte sizes not bit sizes, ie, int2, int4, int8. Or you might prefer smallint, int, bigint. There are no unsigned types. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Foreign key constraint delete fire order
CN [EMAIL PROTECTED] writes: I wish foreign key constraint trigger guarantees that rows in referensing table are deleted before the rows in referenced table is deleted. ... Does my wish make sense or violate any standard? Sorry, the SQL standard says that it happens in the current order (rows deleted as a consequence of RI actions are to be dropped at the *end* of the SQL-statement). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] benchmark suite
On Fri, Sep 29, 2006 at 07:58:01PM +0530, km wrote: ya i have tried the dbt1 (database test 1 - which is what i require) from the ODSL site but couldnt compile as i got the error: cache.c: In function `main': cache.c:134: error: `sname2' undeclared (first use in this function) Is this the very first indication of something being wrong? It sure looks to me like you're missing some headers. -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.1.4 compile problem
At the SQL level, these datatypes are named after byte sizes not bit sizes, ie, int2, int4, int8. Or you might prefer smallint, int, bigint. There are no unsigned types. regards, tom lane oh!! that makes it clear :) thanks! regards, KM ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Expected accuracy of planner statistics
John D. Burger [EMAIL PROTECTED] writes: Tom Lane wrote: IIRC I picked an equation out of the literature partially on the basis of it being simple and fairly cheap to compute... I'm very curious about this - can you recall where you got this, or at least point me to where in the code this happens? src/backend/commands/analyze.c, around line 1930 as of CVS HEAD: /*-- * Estimate the number of distinct values using the estimator * proposed by Haas and Stokes in IBM Research Report RJ 10025: *n*d / (n - f1 + f1*n/N) * where f1 is the number of distinct values that occurred * exactly once in our sample of n rows (from a total of N), * and d is the total number of distinct values in the sample. * This is their Duj1 estimator; the other estimators they * recommend are considerably more complex, and are numerically * very unstable when n is much smaller than N. * * Overwidth values are assumed to have been distinct. *-- */ regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure array limits)
[ expanding this thread, as it now needs wider discussion ] Paul B. Anderson [EMAIL PROTECTED] writes: Actually, I was not filling all of the arrays in sequential order. I added code to initialize them in order and the function seems to be working now. Is that a known problem? Well, it's a documented behavior: section 8.10.4 saith A stored array value can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present. Up to 8.2 we didn't have a lot of choice about this, because without any ability to have nulls embedded in arrays, there wasn't any sane thing to do with the intermediate positions if you assigned to an element not adjacent to the existing range. As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. Comments? regards, tom lane ---(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] Expected accuracy of planner statistics
src/backend/commands/analyze.c, around line 1930 as of CVS HEAD: /*-- * Estimate the number of distinct values using the estimator * proposed by Haas and Stokes in IBM Research Report RJ 10025: Thanks for the pointer, Tom. I shouldn't have been surprised to find such a nice comment pointing me at the literature. - John D. Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure array limits)
On Sep 29, 2006, at 9:14 AM, Tom Lane wrote: [ expanding this thread, as it now needs wider discussion ] Paul B. Anderson [EMAIL PROTECTED] writes: Actually, I was not filling all of the arrays in sequential order. I added code to initialize them in order and the function seems to be working now. Is that a known problem? Well, it's a documented behavior: section 8.10.4 saith A stored array value can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present. Up to 8.2 we didn't have a lot of choice about this, because without any ability to have nulls embedded in arrays, there wasn't any sane thing to do with the intermediate positions if you assigned to an element not adjacent to the existing range. As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. Comments? At first blush, this strikes me as a bit too magical/implicit. Are there other languages where sequences behave similarly? The best analogy that comes to mind is sparse files, but in that case there is an implicit contract that the intervening empty regions do not actually occupy physical space, doesn't sound like that's true here. I think the result of this change would be more difficult debugging of off-by-one errors and their ilk, rather than actually being a real benefit. OTOH, perhaps there is a real use-case I am missing here. I don't see the rest of this thread on GENERAL and I couldn't find it searching the archives, where did it come from? -Casey ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure array limits)
As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. At first blush, this strikes me as a bit too magical/implicit. Are there other languages where sequences behave similarly? perl -e '@A = (1, 2, 3); print @A\n; $A[10] = 10; print @A\n;' 1 2 3 1 2 310 - John D. Burger MITRE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] benchmark suite
Le vendredi 29 septembre 2006 15:54, Andrew Sullivan a écrit : On Fri, Sep 29, 2006 at 07:27:49PM +0530, km wrote: Is there any good benchmark suite for testing postgresql performance? I suggest looking at the excellent software provided by OSDL. http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/ You could also take a look at tsung software : http://tsung.erlang-projects.org/ This distributed load testing tool supports pgsql protocol. You can define some sessions using transactions, requests and think-time, mix them (preferring session A 80% of the time for example), and then configure it to launch as many users as wanted on some interval (1 user a second, for example), during some arrival phases. All this in a XML file. It supports several client machines participating on the same benchmark test, and produces some graphic reports to analyze results thereafter. It also comes with a good documentation. I've beginning to work on dbt2 testing part, re-using its database schema and database populating code from Mark Wong (OSDL). The tricky part here is porting SQL input parameters generator, and trying to have comparable test behavior. I hope to get some usefull results to show soon, including comparing 8.1 and 8.2 versions, for some acceptable value of soon :) Regards, -- Dimitri Fontaine http://www.dalibo.com/ pgp8n94p6LANC.pgp Description: PGP signature
Re: [GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure array
It seems that the suggestion to fill intermediate positions with NULLs would be preferable to the current behavior. I know of no requirement to populate arrays in sequence in any other language so I think other programmers would be surprised too by the current behavior. Paul Tom Lane wrote: [ expanding this thread, as it now needs wider discussion ] "Paul B. Anderson" [EMAIL PROTECTED] writes: Actually, I was not filling all of the arrays in sequential order. I added code to initialize them in order and the function seems to be working now. Is that a known problem? Well, it's a documented behavior: section 8.10.4 saith A stored array value can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present. Up to 8.2 we didn't have a lot of choice about this, because without any ability to have nulls embedded in arrays, there wasn't any sane thing to do with the intermediate positions if you assigned to an element not adjacent to the existing range. As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. Comments? regards, tom lane ---(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] Array assignment behavior (was Re: [ADMIN] Stored procedure
Yep, that definitely threw me the first time I encountered it. Paul B. Anderson wrote: It seems that the suggestion to fill intermediate positions with NULLs would be preferable to the current behavior. I know of no requirement to populate arrays in sequence in any other language so I think other programmers would be surprised too by the current behavior. Paul Tom Lane wrote: [ expanding this thread, as it now needs wider discussion ] Paul B. Anderson [EMAIL PROTECTED] writes: Actually, I was not filling all of the arrays in sequential order. I added code to initialize them in order and the function seems to be working now. Is that a known problem? Well, it's a documented behavior: section 8.10.4 saith A stored array value can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present. Up to 8.2 we didn't have a lot of choice about this, because without any ability to have nulls embedded in arrays, there wasn't any sane thing to do with the intermediate positions if you assigned to an element not adjacent to the existing range. As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. Comments? regards, tom lane ---(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 . -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pg web hosting with tsearch2?
I hope pg-general is the correct forum for this questionif not please let me know the correct location. I have a pg application that uses tsearch2. I would like to move this application off my local machine and onto a web host somewhere. I have some questions regarding this: 1) What is the preferred postgresql text searching tool these days? Is it still tsearch2? 2) Can someone suggest a web host service that includes tsearch2 (or an equivalent text searching component)? 3) All the web hosts I am aware of are still offering only pg 7.4. Does anybody offer pg 8.x ?
Re: [GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure array limits)
John D. Burger [EMAIL PROTECTED] writes: As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. At first blush, this strikes me as a bit too magical/implicit. Are there other languages where sequences behave similarly? perl -e '@A = (1, 2, 3); print @A\n; $A[10] = 10; print @A\n;' 1 2 3 1 2 310 Actually, now that I look closely, I think the SQL spec demands exactly this. Recall that SQL99 only allows one-dimensional, lower-bound-one arrays. The specification for UPDATE ... SET C[I] = SV ... reads Case: i) If the value of C is null, then an exception condition is raised: data exception - null value in array target. ii) Otherwise: 1) Let N be the maximum cardinality of C. 2) Let M be the cardinality of the value of C. 3) Let I be the value of the simple value specification immediately contained in update target. 4) Let EDT be the element type of C. 5) Case: A) If I is greater than zero and less than or equal to M, then the value of C is replaced by an array A with element type EDT and cardinality M derived as follows: I) For j varying from 1 (one) to I-1 and from I+1 to M, the j-th element in A is the value of the j-th element in C. II) The I-th element of A is set to the specified update value, denoted by SV, by applying the General Rules of Subclause 9.2, Store assignment, to the I-th element of A and SV as TARGET and VALUE, respectively. B) If I is greater than M and less than or equal to N, then the value of C is replaced by an array A with element type EDT and cardinality I derived as follows: I) For j varying from 1 (one) to M, the j-th element in A is the value of the j-th element in C. II) For j varying from M+1 to I-1, the j-th element in A is the null value. III) The I-th element of A is set to the specified update value, denoted by SV, by applying the General Rules of Subclause 9.2, Store assignment, to the I-th element of A and SV as TARGET and VALUE, respectively. C) Otherwise, an exception condition is raised: data exception - array element error. We currently violate case i by allowing the null array value to be replaced by a single-element array. I'm disinclined to change that, as I think our behavior is more useful than the spec's. But case ii.5.B pretty clearly describes null-fill, so I think we'd better do that, now that we can. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] using schema's for data separation
On 9/29/06, Just Someone [EMAIL PROTECTED] wrote: I am using a similar solution, and I tested it with a test containing 20K+ different schemas. Postgres didn't show slowness at all even after the 20K (over 2 million total tables) were created. So I have feeling it can grow even more. That's good to know we haven't really tested it against that many schema's, other then actually creating them to make sure there wasn't some sort of hard limit or bug no one had run into before. Performance with schema's is actually one thing I do like. A query for any one user is only hitting the data in the one schema, so users with large data sets don't impact the query performance of users with smaller data sets. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [NOVICE] Do non-sequential primary keys slow performance significantly??
I would expect no performance difference at all. All primary keys automatically get an index, and the index is effectively an optimized dictionary, hash, two-dimensional array, or list of tuples of the key values and the address of the record for that key. Indexes are designed to eliminate the physical performance penalty from arbitrarily large and variable data sets. My only trepidation is using unpredictable values for primary keys. Certainly they're candidate keys and should be unique in the table, but I wouldn't be comfortable using an unpredictable value as a primary key. A surrogate key combined with a unique constraint on your random field seems like a better choice here, but that's entirely a subjective opinion. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Damian C Sent: Friday, September 29, 2006 1:29 AM To: [EMAIL PROTECTED] Subject: [NOVICE] Do non-sequential primary keys slow performance significantly?? Hello, The most difficult part of this question is justifying WHY we would want to use random primary keys! There is a very strong reason for doing so, although not quite compelling. We are Java developers developing desktop applications that persist data in postgres. This is a pretty low spec database as it will only servicing a few PCs. We do this via Hibernate so our SQL Postrges skills and insights are relatively lacking. I certainly don't really understand the gory internal details of postgres. We have an internal proposal to use what are virtually random 128 bit numbers for our primary keys. These are not truley random in any mathematical sense, and they will be unique, but they are certainly NOT sequential. In my ignorant bliss I would suspect that postgres will run more slowly using random primary keys. Can anyone provide any rules of thumb for how this may effect performance?? Is it a plain dumb idea?? Or maybe it would have only modest impact?? Any comments, insights, pointers are very much appreciated, Thanks, -Damian ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg web hosting with tsearch2?
Rick Schumeyer wrote: I hope pg-general is the correct forum for this question.if not please let me know the correct location. I have a pg application that uses tsearch2. I would like to move this application off my local machine and onto a web host somewhere. I have some questions regarding this: 1) What is the preferred postgresql text searching tool these days? Is it still tsearch2? Yes and pg_trgm (similar but different) 2) Can someone suggest a web host service that includes tsearch2 (or an equivalent text searching component)? www.commandprompt.com 3)All the web hosts I am aware of are still offering only pg 7.4. Does anybody offer pg 8.x ? 8.0.x and 8.1.x only. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg web hosting with tsearch2?
I guess I should have mentioned this initially...I also need a web host that offers that other database (my***). We are using the Joomla content management system which only works with my***. Although, I'm not as picky about which version of my*** is offered. I'm examining that commandprompt...my initial guess is they do not support my*** ? -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Friday, September 29, 2006 5:05 PM To: Rick Schumeyer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg web hosting with tsearch2? Rick Schumeyer wrote: I hope pg-general is the correct forum for this question.if not please let me know the correct location. I have a pg application that uses tsearch2. I would like to move this application off my local machine and onto a web host somewhere. I have some questions regarding this: 1) What is the preferred postgresql text searching tool these days? Is it still tsearch2? Yes and pg_trgm (similar but different) 2) Can someone suggest a web host service that includes tsearch2 (or an equivalent text searching component)? www.commandprompt.com 3)All the web hosts I am aware of are still offering only pg 7.4. Does anybody offer pg 8.x ? 8.0.x and 8.1.x only. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(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] pg web hosting with tsearch2?
I don't know if A2webhosting.com specifically supports tsearch2, but they say they offer 8.1 with procedural language installed.JCR- Original Message From: Joshua D. Drake [EMAIL PROTECTED]To: Rick Schumeyer [EMAIL PROTECTED]Cc: pgsql-general@postgresql.orgSent: Friday, September 29, 2006 5:04:51 PMSubject: Re: [GENERAL] pg web hosting with tsearch2?Rick Schumeyer wrote: I hope pg-general is the correct forum for this question.if not please let me know the correct location. I have a pg application that uses tsearch2.I would like to move this application off my local machine and onto a web host somewhere.I have some questions regarding this: 1) What is the preferred postgresql text searching tool these days? Is it still tsearch2?Yes and pg_trgm (similar but different) 2) Can someone suggest a web host service that includes tsearch2 (or an equivalent text searching component)?www.commandprompt.com 3)All the web hosts I am aware of are still offering only pg 7.4. Does anybody offer pg 8.x ?8.0.x and 8.1.x only.Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. ===Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensivePostgreSQL solutions since 1997 http://www.commandprompt.com/---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg web hosting with tsearch2?
Rick Schumeyer wrote: I guess I should have mentioned this initially...I also need a web host that offers that other database (my***). We are using the Joomla content Well that pretty much blows us out of the water :). I'm examining that commandprompt...my initial guess is they do not support my*** ? No guessing about it from the site: MySQL Databases * We support MySQL for migration purposes only. If you need MySQL support, please contact us to discuss your migration plans. Also, MySQL is only available with dedicated hosting services. So if you have a migration plan, we are 100% happy to help you :) Sincerely, Joshua D. Drake Command Prompt, Inc. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Friday, September 29, 2006 5:05 PM To: Rick Schumeyer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg web hosting with tsearch2? Rick Schumeyer wrote: I hope pg-general is the correct forum for this question.if not please let me know the correct location. I have a pg application that uses tsearch2. I would like to move this application off my local machine and onto a web host somewhere. I have some questions regarding this: 1) What is the preferred postgresql text searching tool these days? Is it still tsearch2? Yes and pg_trgm (similar but different) 2) Can someone suggest a web host service that includes tsearch2 (or an equivalent text searching component)? www.commandprompt.com 3)All the web hosts I am aware of are still offering only pg 7.4. Does anybody offer pg 8.x ? 8.0.x and 8.1.x only. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure
Ok, just so I can be sure I understand what I just read: i. says that you can assign to an array that has not been initialized. ii. states that the index of an insertion into an array should not be limited by the current range of index values of the array and requires any gaps in the index range to be set with values of null. I really don't have anything to add to discussion other than that I agree with Tom's assessment, but rather want clarify what might be a slightly hazy interpretation of the specs listed below. Tom Lane wrote: Actually, now that I look closely, I think the SQL spec demands exactly this. Recall that SQL99 only allows one-dimensional, lower-bound-one arrays. The specification for UPDATE ... SET C[I] = SV ... reads Case: i) If the value of C is null, then an exception condition is raised: data exception - null value in array target. ii) Otherwise: 1) Let N be the maximum cardinality of C. 2) Let M be the cardinality of the value of C. 3) Let I be the value of the simple value specification immediately contained in update target. 4) Let EDT be the element type of C. 5) Case: A) If I is greater than zero and less than or equal to M, then the value of C is replaced by an array A with element type EDT and cardinality M derived as follows: I) For j varying from 1 (one) to I-1 and from I+1 to M, the j-th element in A is the value of the j-th element in C. II) The I-th element of A is set to the specified update value, denoted by SV, by applying the General Rules of Subclause 9.2, Store assignment, to the I-th element of A and SV as TARGET and VALUE, respectively. B) If I is greater than M and less than or equal to N, then the value of C is replaced by an array A with element type EDT and cardinality I derived as follows: I) For j varying from 1 (one) to M, the j-th element in A is the value of the j-th element in C. II) For j varying from M+1 to I-1, the j-th element in A is the null value. III) The I-th element of A is set to the specified update value, denoted by SV, by applying the General Rules of Subclause 9.2, Store assignment, to the I-th element of A and SV as TARGET and VALUE, respectively. C) Otherwise, an exception condition is raised: data exception - array element error. We currently violate case i by allowing the null array value to be replaced by a single-element array. I'm disinclined to change that, as I think our behavior is more useful than the spec's. But case ii.5.B pretty clearly describes null-fill, so I think we'd better do that, now that we can. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Backslashes in 8.2 still escape, hwo to disable?
Hi, I'm trying out the new 8.2 beta; the following query: select 'ab\cd'; yields: abcd, not ab\cd. Is there a setting I'd need to switch to enable the ANSI/ISO escape behavior? I am using the JDBC driver; not sure if that could be the culprit.. Thanks, Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Backslashes in 8.2 still escape, hwo to disable?
On Fri, 2006-09-29 at 18:12 -0600, Ken Johanson wrote: Hi, I'm trying out the new 8.2 beta; the following query: select 'ab\cd'; yields: abcd, not ab\cd. Is there a setting I'd need to switch to enable the ANSI/ISO escape behavior? I am using the JDBC driver; not sure if that could be the culprit.. standard_conforming_strings Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] using schema's for data separation
On Fri, 2006-09-29 at 09:39 -0500, Erik Jones wrote: What we've done is to use table inheritance. So, we have a group of base account data tables and whenever a new account is added they get a set of tables that inherit from these base tables. This works well in that whenever we need a global schema change to any of these tables we can just alter that pertinent base table and the change will cascade down to the child tables. Many DDL commands do not propagate to the child tables -- foreign keys, for example, are not inherited. As a consequence, using inheritance for Chris' purpose has the important caveat that referential integrity will be difficult to ensure with standard FK constraints. I believe that other types of constraints and rules are also not propagated to child tables. Erik- do you have a clever solution to this aspect of using inheritance? On Thu, 2006-09-28 at 22:59 -0700, snacktime wrote: The connections to the database are from an unprivileged user, and everything goes through functions that run at the necessary privileges. Someone out there must have implemented row-level security for postgresql (EnterpriseDB?). You could implement this yourself by adding a usesysid column to each table and using a combination of column defaults, views, triggers, or rules to set usesysid on insert and update and to require that the usesysid column matches the current user on select and delete. This probably improves the consistency of your security policy (over having the policy in functions). On the other hand, I could be blowing smoke -- it seems like this outta work, but I haven't actually done this myself. I don't actually do any real work myself. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(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] PG Rocks! (was:Backslashes in 8.2 still escape, hwo
You guys, and PG ROCK!! The standard_conforming_strings mode works beautifully... it is (was) the last low-level barrier to using PG in a really professional/interop environment. And as I become familiar, again, with how PG and it's tools have come along, I am SUPER impressed. Many, many kudos!! Top notch!! Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
