[GENERAL] function in index expression and unnecessary function calls in select
Hi,
I have Linux, PostgreSQL 8.1, Python 2.3
pgsql code:
test=# create table t1(name varchar(10), value integer);
CREATE TABLE
test=# create or replace function f1(integer) returns integer as $$
test$# file = open('/tmp/f1.log', 'a')
test$# file.write('log\n')
test$# file.close
test$# return args[0]+1
test$# $$ language plpythonu immutable;
CREATE FUNCTION
test=# select name, f1(value) from t1;
name | f1
--+
r1 | 2
r2 | 3
(records: 2)
$ cat /tmp/f1.log
log
log
After creating index:
test=# create index i1 on t1 (f1(value));
CREATE INDEX
$ cat /tmp/f1.log
log
log
log
log
After select:
test=# select name, f1(value) from t1;
name | f1
--+
r1 | 2
r2 | 3
(records: 2)
$ cat /tmp/f1.log
log
log
log
log
log
log
Why f1 was called in last case? Why select can't use index values
instead of function call results? Is it bug? Can it be configured?
--
Thanks,
Eugene Prokopiev
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] int to inet conversion
Tom Lane wrote: Anton Nikiforov [EMAIL PROTECTED] writes: is there any function that can translate INT to INET type? Nothing built-in, and given the fact that inet no longer means IPv4, it's unlikely we'd add one in the future. But there's nothing stopping you from adding one of your own. For example regression=# create or replace function int2inet(int) returns inet as $$ regression$# declare oct1 int; regression$# oct2 int; regression$# oct3 int; regression$# oct4 int; regression$# begin regression$# oct1 := ((($1 24) % 256) + 256) % 256; regression$# oct2 := ((($1 16) % 256) + 256) % 256; regression$# oct3 := ((($1 8) % 256) + 256) % 256; regression$# oct4 := ((($1 ) % 256) + 256) % 256; regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4; regression$# end$$ language plpgsql strict immutable; CREATE FUNCTION regression=# select int2inet(-1062726656); int2inet -- 192.168.20.0 (1 row) There's probably a better way to do the shifting-and-masking, but that was the first thing that came to mind. (Actually, if you are planning to push a whole lot of data through this, it might be worth your time to write something in C. But for a one-shot data conversion task this is probably plenty good enough.) 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 Thanks alot, Mr. Lane i was just thinking that there was something inside postgres to convert this types. But now will try to write this functions :) Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] function in index expression and unnecessary function calls in select
On Sun, Dec 04, 2005 at 01:00:52PM +0300, Eugene Prokopiev wrote: Hi, I have Linux, PostgreSQL 8.1, Python 2.3 snip test=# select name, f1(value) from t1; name | f1 --+ r1 | 2 r2 | 3 (records: 2) $ cat /tmp/f1.log log log log log log log Why f1 was called in last case? Why select can't use index values instead of function call results? Is it bug? Can it be configured? Because an index is for sorting or finding rows in a table, not for storing data. Function indexes are to make is quicker to find certain types of data. The index may not even store the results of f1(). Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpwBcmZWWvz1.pgp Description: PGP signature
Re: [GENERAL] int to inet conversion
Tom Lane wrote: Anton Nikiforov [EMAIL PROTECTED] writes: is there any function that can translate INT to INET type? Nothing built-in, and given the fact that inet no longer means IPv4, it's unlikely we'd add one in the future. But there's nothing stopping you from adding one of your own. For example regression=# create or replace function int2inet(int) returns inet as $$ regression$# declare oct1 int; regression$# oct2 int; regression$# oct3 int; regression$# oct4 int; regression$# begin regression$# oct1 := ((($1 24) % 256) + 256) % 256; regression$# oct2 := ((($1 16) % 256) + 256) % 256; regression$# oct3 := ((($1 8) % 256) + 256) % 256; regression$# oct4 := ((($1 ) % 256) + 256) % 256; regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4; regression$# end$$ language plpgsql strict immutable; CREATE FUNCTION regression=# select int2inet(-1062726656); int2inet -- 192.168.20.0 (1 row) There's probably a better way to do the shifting-and-masking, but that was the first thing that came to mind. (Actually, if you are planning to push a whole lot of data through this, it might be worth your time to write something in C. But for a one-shot data conversion task this is probably plenty good enough.) 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 Sorry for my stupidity but, maybe there is a function that converts mask stored in int format to a numer of bits? ;) Your function easyly convert this mask to dot decimal notation, but how to count the number of 1 in it? Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
On Sun, Dec 04, 2005 at 02:09:53PM +0300, Anton Nikiforov wrote: Sorry for my stupidity but, maybe there is a function that converts mask stored in int format to a numer of bits? ;) Your function easyly convert this mask to dot decimal notation, but how to count the number of 1 in it? No, but you can write one the same way like so: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t 0 and (t i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpcstlpgc6iQ.pgp Description: PGP signature
Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1
Have you tried to restore just schema first, then data? Greetings, Zlatko - Original Message - From: Howard Cole [EMAIL PROTECTED] To: 'PgSql General' pgsql-general@postgresql.org Sent: Friday, December 02, 2005 3:02 PM Subject: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1 Hi everyone, I have a problem with corrupt UTF-8 sequences in my 8.0.4 dump which is preventing me from upgrading to 8.1 - which spots the errors and refuses to import the data. Is there some SQL command that I can use to fix or cauterise the sequences in the 8.0.4 database before dumping to 8.1? I think the problem arose using invalid client encodings - which were not rejected prior to 8.1. Regards, Howard Cole www.selestial.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Fwd: Enhancement Request : Expressions for format string in PlPgsql RAISE statement
Hello there, Unfortunately, as I am not a C hacker, I won't be able to do that by myself. Do you think it is an interesting feature or not ? Is anybody else interested in it ? Should I send it to hackers list for TODO item creation request ? Thanks. -- Message transmis -- Subject: Enhancement Request : Expressions for format string in PlPgsql RAISE statement Date: Samedi 29 Octobre 2005 17:12 From: Laurent HERVE [EMAIL PROTECTED] To: pgsql-general@postgresql.org Hi, I don't know where to submit enhancement requests. So I'm doing there. I have to send error messages in several languages within PlPgSQL code. I would like RAISE to work like that : RAISE EXCEPTION get_my_format_string(MY_ERR_NBR,USER_LANG),a,b,a+b/c; where the get_my_format_string returns a format string understandable for RAISE (like 'initial values = % and % result = %') for each valid combination of MY_ERR_NBR (my application error number) and USER_LANG (the language in which I would like the error string to be displayed). I think this would be really useful to add international support to my PostgreSQL applications. It seems the 8.1 RAISE statement does not provide such a possibility. Regards, --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] int to inet conversion
Martijn van Oosterhout wrote: On Sun, Dec 04, 2005 at 02:09:53PM +0300, Anton Nikiforov wrote: Sorry for my stupidity but, maybe there is a function that converts mask stored in int format to a numer of bits? ;) Your function easyly convert this mask to dot decimal notation, but how to count the number of 1 in it? No, but you can write one the same way like so: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t 0 and (t i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, Sorry, did not quite catch. t in this case is int, and there is no log2(int) function. Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Slow COUNT
Am Sonntag, den 04.12.2005, 14:02 +0100 schrieb Guido Neitzer: On 02.12.2005, at 20:02 Uhr, Jaime Casanova wrote: so the way to do it is create a trigger that record in a table the number of rows... As there are SO MANY questions about the count(*) issue, I wonder whether it makes sense to add a mechanism which does exactly the method mentioned above in a default PostgreSQL installation (perhaps switched of by default for other performance impacts)?! I dont think this would match postgres style - to include a kludge for a rarely usefull special case. I may be wrong but personally I never needed unqualified count(*) on a table to be very fast. Doing something to enable aggregates in general to use an existent index would be a nice ide imho. (With all the visibility hinting in place) Just my 0.02Ct. ++Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] int to inet conversion
On Sun, Dec 04, 2005 at 03:21:47PM +0300, Anton Nikiforov wrote: Martijn van Oosterhout wrote: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t 0 and (t i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, Sorry, did not quite catch. t in this case is int, and there is no log2(int) function. But there is a log(x,y) function, so log(2,t) would work also. Note that 255.255.255.0 stored as integer is -256. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpFjv4s9DaQo.pgp Description: PGP signature
Re: [GENERAL] Slow COUNT
On Sun, Dec 04, 2005 at 14:40:49 +0100, Tino Wildenhain [EMAIL PROTECTED] wrote: Doing something to enable aggregates in general to use an existent index would be a nice ide imho. (With all the visibility hinting in place) Assuming you are refering to max and min, this has already been done and is in 8.1. ---(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] Use of %ROWTYPE in plpgsql function declarations
Karl O. Pinc [EMAIL PROTECTED] writes: ... It just seemed a little wierd not to have the %ROWTYPE because AFIK you need it when declaring in plpgsql's DECLARE No, you don't, as 36.4.3 says perfectly clearly. %ROWTYPE is an Oracle-ism that we support inside plpgsql, but not elsewhere. 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] Slow COUNT
Am Sonntag, den 04.12.2005, 09:56 -0600 schrieb Bruno Wolff III: On Sun, Dec 04, 2005 at 14:40:49 +0100, Tino Wildenhain [EMAIL PROTECTED] wrote: Doing something to enable aggregates in general to use an existent index would be a nice ide imho. (With all the visibility hinting in place) Assuming you are refering to max and min, this has already been done and is in 8.1. I also mean sum, avg, ... and last not least count :-) Thx for info though. ++Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] postgresql jdbc connect via hostname instead of just ip
Hi all, I'm a bit new to postgresql. I've been able to configure the database and the jdbc interface successfully. I interface via PAM (krb5) and that works OK in the pg_hba.conf file. I've added the -i option to the postmaster startup script service (using binary RPMs on Fedora Core 4) for tcp connections (default port 5432). The only way I can connect to the database via JDBC seems to be by IP address (both locally and on another system allowed by the pg_hba.conf file). jdbc:postgresql://theipaddressofdatabaseserver/mydatabase I'd like to be able to connect to the database this way using a DNS name / computer host name. The specific error I recieve is on the lines of no route to host. I'm sure I'm just missing a simple setting somewhere. Can anyone point me in the right direction? pg_hba.conf host mydatabase all 127.0.0.1/32 pam host mydatabase all theipsubnet/24 pam Many thanks, Jonathan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql jdbc connect via hostname instead of just ip
Jonathan Schreiter [EMAIL PROTECTED] writes: The only way I can connect to the database via JDBC seems to be by IP address (both locally and on another system allowed by the pg_hba.conf file). jdbc:postgresql://theipaddressofdatabaseserver/mydatabase I'd like to be able to connect to the database this way using a DNS name / computer host name. The specific error I recieve is on the lines of no route to host. This implies that the IP address in DNS or /etc/hosts is different from the IP address you're using in the URL. It's almost certainly not a Postgres misconfiguration. What happens when you do telnet server.host.name 5432 on the JDBC client machine? -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Selecting Large Object and TOAST
hi, We are storing the Icons/IMages in the database as Large Objects using lo_import functions. (1) what would be the return type if i want to return a large object (XYZ.gif) to the remote client (GUI) using stored procedure. Can anyone give an example please? Are there any size limitations i need to consider when returning Large Object using procedures? (2) A statement from documentation: PostgreSQL 7.1 introduced a mechanism (nicknamed TOAST) that allows data values to be much larger than single pages. This makes the large object facility partially obsolete. How do i TOAST my data stored as Large Object? thanks, vish
Re: [GENERAL] Slow COUNT
Tino Wildenhain [EMAIL PROTECTED] writes: Am Sonntag, den 04.12.2005, 09:56 -0600 schrieb Bruno Wolff III: On Sun, Dec 04, 2005 at 14:40:49 +0100, Assuming you are refering to max and min, this has already been done and is in 8.1. I also mean sum, avg, ... and last not least count :-) The naive implementation would mean serializing all table updates. In other words only one person can update, insert, or delete at a time. Until that user commits everybody else would be locked out of the table. You may as well be using something like mysql then if that's acceptable. The more sophisticated implementation would require customization to get right. It requires a second table keeping track of deltas and a periodic job aggregating those deltas. Which aggregates to put in it, how often to aggregate them, and when to consult them instead of consulting the main table would all be things that would require human intervention to get right. It would be cool if there were a shrinkwrapped package, perhaps in contrib, to do this with knobs for the user to play with instead of having to roll your own. perhaps in contrib. But nobody's done a good enough version yet to consider it. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Creating then dropping primary key constraint
Hi, I noticed that when I create a primary key with ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY (...), and then drop this constraint, then the not null modifier stays on the column on which the primary key was defined although there were no constraint on that column before. Is this normal? Pg 8.0.4 create table pritest(id integer); \d pritest alter table pritest add constraint pk_pritest primary key (id); \d pritest alter table pritest drop constraint pk_pritest; \d pritest drop table pritest; Best regards, Otto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.1, OID`s and plpgsql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Related to the original question though, is there actually any way to get the ctid of a row that was just inserted? No. You'd have to identify the rows some other way (a sequence is the canonical way), and then grab the ctid from that. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200512042018 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDk5WBvJuQZxSWSsgRAnaaAKDswxUhZH4wHAJJDTZSBtTVNY/9/gCgk3La KWRzIVIeamQZvhr+TaFp4RY= =Nevb -END PGP SIGNATURE- ---(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] postgresql jdbc connect via hostname instead of just ip
Thanks for the help - it was a dns resolving issue after all as you described. Jonathan --- Douglas McNaught [EMAIL PROTECTED] wrote: Jonathan Schreiter [EMAIL PROTECTED] writes: The only way I can connect to the database via JDBC seems to be by IP address (both locally and on another system allowed by the pg_hba.conf file). jdbc:postgresql://theipaddressofdatabaseserver/mydatabase I'd like to be able to connect to the database this way using a DNS name / computer host name. The specific error I recieve is on the lines of no route to host. This implies that the IP address in DNS or /etc/hosts is different from the IP address you're using in the URL. It's almost certainly not a Postgres misconfiguration. What happens when you do telnet server.host.name 5432 on the JDBC client machine? -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] int to inet conversion
Martijn van Oosterhout wrote: On Sun, Dec 04, 2005 at 03:21:47PM +0300, Anton Nikiforov wrote: Martijn van Oosterhout wrote: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t 0 and (t i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, Sorry, did not quite catch. t in this case is int, and there is no log2(int) function. But there is a log(x,y) function, so log(2,t) would work also. Note that 255.255.255.0 stored as integer is -256. Have a nice day, Thanks alot! Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Some rare questions
Hi, I am writing a driver for PostgreSQL, and I need some rare info: How can I query the collation/locale of the database cluster? What can be the maximal length of the indexed part of the string. So I have a text field, and I create an index on it. How long can be one index key max in this case? How deep can be the subquery nesting? How long can be the index key? What is maximal number of compare operations for a single query? What is maximal length of a query text (characters) ? What is maximal length of a row (bytes) ? What is the maximal length of char/varchar/text ? What operations cannot be rolled back with Rollback ? Thanks in advance, Otto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Selecting Large Object and TOAST
On 12/4/2005 7:55 PM, vishal saberwal wrote: hi, We are storing the Icons/IMages in the database as Large Objects using lo_import functions. (1) what would be the return type if i want to return a large object ( XYZ.gif) to the remote client (GUI) using stored procedure. Can anyone give an example please? Are there any size limitations i need to consider when returning Large Object using procedures? (2) A statement from documentation: PostgreSQL 7.1 introduced a mechanism (nicknamed TOAST) that allows data values to be much larger than single pages. This makes the large object facility partially obsolete. How do i TOAST my data stored as Large Object? You don't. You would change you schema and application to store the images in bytea columns instead. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Some rare questions
The answers to these are either unlimited or mentioned in the FAQ. --- Havasv?lgyi Ott? wrote: Hi, I am writing a driver for PostgreSQL, and I need some rare info: How can I query the collation/locale of the database cluster? What can be the maximal length of the indexed part of the string. So I have a text field, and I create an index on it. How long can be one index key max in this case? How deep can be the subquery nesting? How long can be the index key? What is maximal number of compare operations for a single query? What is maximal length of a query text (characters) ? What is maximal length of a row (bytes) ? What is the maximal length of char/varchar/text ? What operations cannot be rolled back with Rollback ? Thanks in advance, Otto ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Selecting Large Object and TOAST
On 12/4/2005 9:24 PM, Joshua D. Drake wrote: (1) what would be the return type if i want to return a large object ( XYZ.gif) to the remote client (GUI) using stored procedure. Can anyone give an example please? Are there any size limitations i need to consider when returning Large Object using procedures? You have to use a lookup table that correlates the meta information (filename, content-type) with a particular loid. That way you can store any binary you want. This doesn't answer the question. Fact is that most procedural languages (including PL/pgSQL) don't have any access to classic large objects in the first place. So all the stored procedure can do is to return the identifier of the large object to the client and the client must then use lo_open(), lo_read() etc. to actually get the data of the object. Not all client interfaces support these fastpath based libpq functions. How do i TOAST my data stored as Large Object? This isn't a concern as it is all internal and automatic. You don't. You would change you schema and application to store the images in bytea columns instead. Well I have to disagree with this. It entirely depends on the size of the data you are storing. Bytea is remarkably innefficient. Which would be the data type of your choice for images? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Slow COUNT
On Sun, Dec 04, 2005 at 18:28:53 +0100, Tino Wildenhain [EMAIL PROTECTED] wrote: Am Sonntag, den 04.12.2005, 09:56 -0600 schrieb Bruno Wolff III: On Sun, Dec 04, 2005 at 14:40:49 +0100, Tino Wildenhain [EMAIL PROTECTED] wrote: Doing something to enable aggregates in general to use an existent index would be a nice ide imho. (With all the visibility hinting in place) Assuming you are refering to max and min, this has already been done and is in 8.1. I also mean sum, avg, ... and last not least count :-) Your comment about indexes threw me there. Indexes are not the problem. If you use a WHERE clause with enough selectivity and the is an appropiate index, an an index scan will be used. There is a related issue that when postgres does an index scan, it also needs to visit the hep to check visibility. The issue there is that maintaining visibility in the index has costs that are currently believed to outweigh the benefits of not having to check visibility in the heap. (Though recently there have been some new suggestions in this area.) What you are looking for seems to be caching values for the case where the full table is selected. That has problems as described in the other response and in more details in the archives. This isn't something you want turned on by default, but it would be nice if there was something packaged to make doing this easier for people who want it for selected tables. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Selecting Large Object and TOAST
This doesn't answer the question. Fact is that most procedural languages (including PL/pgSQL) don't have any access to classic large objects in the first place. So all the stored procedure can do is to return the identifier of the large object to the client and the client must then use lo_open(), lo_read() etc. to actually get the data of the object. Not all client interfaces support these fastpath based libpq functions. You are correct, I missed the part about wanting to return from a stored procedure. Well I have to disagree with this. It entirely depends on the size of the data you are storing. Bytea is remarkably innefficient. Which would be the data type of your choice for images? Well as I said it depends on the size of the data. Are we talking 100 meg vector images? Then large objects. Are we talking thumbnails that are 32k then bytea. Joshua D. Drake Jan ---(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] Selecting Large Object and TOAST
On 12/4/2005 11:45 PM, Joshua D. Drake wrote: Well as I said it depends on the size of the data. Are we talking 100 meg vector images? Then large objects. Are we talking thumbnails that are 32k then bytea. I'd say that anything up to a megabyte or so can easily live in bytea. Beyond that it depends on the access pattern. That said, for certain situations I think some sql-callable functions would be very handy: lo_get(oid) returns bytea lo_set(oid, bytea) returns void lo_ins(bytea) returns oid lo_del(oid) returns void Those (and maybe some more) would allow access of traditional large objects through client interfaces that don't support the regular large object calls. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Inheritance Algebra
[ This post is theory oriented, so it can't go in HACKERS nor can it go in SQL so it gets posted to GENERAL. I would polish this more. Unfortunately, it is at the point were I'd seek feedback were I in a seminar. ] Relational Constraint Inheritance Algebra With regard to class and attribute uniqueness 0 Intro: Postgresql inheritance and uniqueness Postgresql's INHERITS is one of the most intriguing features of the at-liberty, open-source database. At the same time, most observers regard INHERITS as an incomplete implementation of a fully object-oriented, or better, class-aware, database function. The most glaring omission is that primary key and unique constraints are not inherited by children. Nevertheless, the implementation of INHERITS has not changed much through the last several revisions of Postgresql. Bizgres' partitioning scheme, constraint based exclusion [?], relies on the current default behavior of inheritance in Postgresql. No doubt other consumers have taken advantage of the feature's current behavior, so any extension must preserve existing behavior by either developing sub-clauses that further specify the behavior of the INHERITS or they must develop an entirely new lexis for building inheritance based relational classes. When a constraint is declared in a database that supports relational inheritance, the constraint necessarily has scope. In the simplest cases, constraint scope is local, applying only to the table where the constraint was declared, or the scope is to the subclass, applying to this table and all descendants unless over-ridden. According to the Postgresql 8.0 documentation, all constraints are automatically inherited unless over-ridden (the subclass model) except for foreign and unique constraints that are unsupported at the class level. In effect, under Postgresql 8.0 foreign and unique constraints have local scope. Another notable quirk of Postgresql's inheritance model is that no table is explicitly aware it could become a parent. There is no “abstract” or “final” clause nor any other clause restricting the behavior or potential children exists in “CREATE TABLE”. Indeed, the top of any inheritance hierarchy necessarily begins as a strictly relational table. One side effect of the current model is that implementing class-wide uniqueness is problematic. Either the parent model would need to be abstract (a nonexistent clause) or a child's inheritance of a unique constraint would change the behavior of the parents heretofore table-local unique (or even non-unique) column. Postgresql's current hybrid implementation of inheritance, having both implicitly local and subclass scope for different kinds of constraints, points to a powerful hybrid model where columns can have constraints that are explicitly declared with table-local or subclass-wide scopes. The rest of this essay examines the interaction of localism-class cross plurality-uniqueness[1]. It seems obvious that the distinctions have theoretical discussion (and hopefully acceptance). More important is whether the supporting these distinctions would be useful in any real-world product. I believe that supporting such fine distinctions would be of some use, but will make no further effort to argue the case. 1 Types of relational inheritance models Relational inheritance of a constraint feature has scope [2]. Levels of scope include absent (necessarily local), table-local, subclass, class-wide, mixed, and dual. Obviously, support for relational inheritance can simply be absent. This is the norm. Any such table is strictly relational and all constraints are necessarily local. Tables in this essay are explicitly not under the “absent” relational inheritance scope. Another family of models for relational inheritance scope might be called local (table-local or relation-local). If Postgresql's CREATE TABLE ... LIKE clause allowed for “inheritance” of all constraints, triggers, and so on, it would be an implementation of the local model. In particular, unique constraints are checked for each table in the class but are not enforced over the whole of an entire class or subclass. Presumably, if table-local scope were the default behavior across a database, queries would not recurse into descendant tables by default. Note that this used to be Postgresql's default behavior. SQL developers had to ask the engine to recurse into descendant tables. Mixed scope models extend the local model, allowing for class-like treatment of some relational aspects. (In this essay we are particularly concerned with plurality-uniqueness.) Arguably (and unfortunately), Postgresql currently implements a mixed model. Some constraints have subclass scope and some have local scope. A traditional, strictly hierarchical inheritance of constraints from object-aware tables by descendants is a powerful scoping model. Strictly speaking, every table
