[SQL] Blobs with perl
I want to insert a picture in a table from an internet browser using a script made in perl. Has somebody a code example with this kind a problem (I need only a code fragment)? Thanks! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Catching DML exceptions in PL/pgSQL
Hello all, (and sorry if this has been aswered before) Take this piece of code for example: . begin _res.code:=1; select id into iid from log where id=_id; if not found then begin _res.msg:=''insert''; insert into log (log, data) values (_log, _data); if not found then begin _res.msg:=_res.msg || '' error''; _res.code:=-1; end; end if; end; else begin . The thing is if _data (parameter) is null and table has a (data <> null) check, the insert would fail and abort the function before my "if not found" test. I'm porting a java app. from mssql to postgresql, and the java code relies on the stored procedure to always return it's status (in _res.code in this case). Is there anything I can do to make sure the function always returns _res ? Something along the lines of Oracle's exception handling, or the @@error trick in mssql ? Regards, -- Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd.
Re: [SQL] Catching DML exceptions in PL/pgSQL
Dnia 2003-06-17 11:25, Użytkownik Radu-Adrian Popescu napisał: Hello all, (and sorry if this has been aswered before) Take this piece of code for example: . begin _res.code:=1; select id into iid from log where id=_id; if not found then begin _res.msg:=''insert''; *insert into log (log, data) values (_log, _data); if not found* then begin _res.msg:=_res.msg || '' error''; _res.code:=-1; end; end if; end; else begin . The thing is if _data (parameter) is null and table has a (data <> null) check, the insert would fail and abort the function before my "if not found" test. I'm porting a java app. from mssql to postgresql, and the java code relies on the stored procedure to always return it's status (in _res.code in this case). Is there anything I can do to make sure the function always returns _res ? Something along the lines of Oracle's exception handling, or the @@error trick in mssql ? You can't do it this way. Your insert is violation of some constraint (problably "not null" or "primary key" constraint). This kind of violation raises exception and whole transaction is aborted. I don't use java, but C librares raises also ordinary C exception which can be easily caught. If you want to avoid such cases - check your data before inserting them. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Catching DML exceptions in PL/pgSQL
At 6/17/2003 11:44, Tomasz Myrta wrote: Dnia 2003-06-17 11:25, U¿ytkownik Radu-Adrian Popescu napisa³: Hello all, (and sorry if this has been aswered before) Take this piece of code for example: . begin _res.code:=1; select id into iid from log where id=_id; if not found then begin _res.msg:=''insert''; *insert into log (log, data) values (_log, _data); if not found* then begin _res.msg:=_res.msg || '' error''; _res.code:=-1; end; end if; end; else begin . The thing is if _data (parameter) is null and table has a (data <> null) check, the insert would fail and abort the function before my "if not found" test. I'm porting a java app. from mssql to postgresql, and the java code relies on the stored procedure to always return it's status (in _res.code in this case). Is there anything I can do to make sure the function always returns _res ? Something along the lines of Oracle's exception handling, or the @@error trick in mssql ? You can't do it this way. Your insert is violation of some constraint (problably "not null" or "primary key" constraint). This kind of violation raises exception and whole transaction is aborted. I don't use java, but C librares raises also ordinary C exception which can be easily caught. If you want to avoid such cases - check your data before inserting them. Thanks, unfortunatelly I knew that... My java/db code currently decides whether the call was successful based on 1) return from stored procedure (currently, to be replaced by select * from function_name(...)) 2) SQLException So basically it would work no probs: i.e. instead of reading a -1 when an error occured, there would be an SQLException caught and the final outcome, either way, would be that the java method would still function properly and let the calling code know whether things went okay or not. However, the point was to be able to deal with these exceptions inside pl/plgsql, as it would bring imho a load more power and fexibility to the db code. For instance, it could choose to log failures to a database table, or choose another execution path and so on. Anyway, thanks for the reply :-) -- Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Catching DML exceptions in PL/pgSQL
Radu-Adrian Popescu wrote: . begin _res.code:=1; select id into iid from log where id=_id; if not found then begin _res.msg:=''insert''; *insert into log (log, data) values (_log, _data); if not found* then begin _res.msg:=_res.msg || '' error''; _res.code:=-1; end; end if; end; else begin . The thing is if _data (parameter) is null and table has a (data <> null) check, the insert would fail and abort the function before my "if not found" test. You could test for _data is null, and if so check attnotnull in pg_attribute. E.g. something like: declare iattnotnull bool [...] begin if _data is null then select into iattnotnull attnotnull from pg_catalog.pg_attribute where attrelid = 'log'::regclass and attname = 'data'; if iattnotnull then _res.code := -1; [...] Is there anything I can do to make sure the function always returns _res ? Something along the lines of Oracle's exception handling, or the @@error trick in mssql ? There is currently no way to "catch" the exception in PL/pgSQL, but maybe the above would work for you. HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Catching DML exceptions in PL/pgSQL
Joe, Tomasz, Thanks for the reply. In any event, like I said, my java code should work with the functions as they are now, whether db throws an exception or returns some result. Also, the oracle-like exception handling would be really great and i know i'm looking forward to it ! Maybe in 7.5 ? :-) Cheers, -- Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help
Josh, thanks for your help. Josh Berkus wrote: Tony, I've a query which needs too many time ca. 12-15 sec. how can i get a better perfomance? First of all, please take this to the PGSQL-PERFORMANCE list. Second, see this web page: http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines i read some tips on the above pages and my database and all my queries run very well. i've optimized the queriey from where ... IN (select ..) to EXISTS and I've done vacumdb full. my requests last between 0.009 to 0.2 sec compared with 12-15 sec. i'm very happy. thanks very much. tony explain select o.id from ioobeject o,dist_vertron v where macro_lid=1123 and (o.id=v.id) and (o.deleted<>'1') and (o.status='activo') order by o.ort; Third, from your explain, ioobject and dist_vertron are obviously somewhat complex views. We need those view definitions, possibly plus schema for the underlying tables (including indexes), or we can't help you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] select date range?
On Tue, 17 Jun 2003, ko wrote: > Hi, > > When I use sql statement to select the date range from $_POST value,It doesn't work. > > --- > "select * from mydatabase where mydate between '$_POST[start_date]' and > '$_POST[end_date]' " > > There is no error,but the result is not I want. > > ps: > (1)mydate attribute is timestamp > (2)$_POST[start_date] is something like '2003/05/12' You may be having problems with how PHP interprets (or more correctly, doesn't) arrays inside of strings. the problem is that inside of a string the autointerpretation of a string won't work for arrays, only simple scalars. Change your string to this: "select * from mydatabase where mydate between '".$_POST[start_date]."' and '".$_POST[end_date]."'" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] yet pg_toast reindex
When I try to reindex a toast table with command ´REINDEX TABLE pg_toast_16557' I receive following error message: ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone postgres with -O -P options So I restart postmaster standalone (no -i option) and I receive same error. how could I reindex it? tnx Eric Anderson Martins Miranda Net Admin @ Via Net SAO ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Blobs with perl
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 17 June 2003 02:00, cristi wrote: > I want to insert a picture in a table from an internet browser using a > script made in perl. > Has somebody a code example with this kind a problem (I need only a code > fragment)? > Despite PostgreSQL's powerful BLOB features, I would strongly suggest against storing these kind of things in the database. It is better to have it in a local file for several reasons. 1) Apache can server up local images lightning fast 2) You can edit local images with your favorite image editor (ie, Gimp, Photoshop) 3) You can ftp, scp, sftp the image around without a problem. 4) You can tar it up and archive it. 5) You can move it off of your burdened PostgreSQL database server machine and on to its own image server when your site becomes popular. While all of the above are certainly possible with PostgreSQL, it is a bit more complicated. And remember, while your database server is small now, it will grow, and grow, and grow, and grow. It will one day become the bottleneck in your operations. That is an inevitable fact of any dynamic website. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+7yYTWgwF3QvpWNwRAv3WAKDFrjfQUpQFmZFvVMismUeoxABoDQCfY/F7 LajclhvacQOgsn+6qnLEEwQ= =k0vW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] yet pg_toast reindex
"Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > When I try to reindex a toast table with command ´REINDEX TABLE > pg_toast_16557' I receive following error message: > ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone > postgres with -O -P options > So I restart postmaster standalone (no -i option) and I receive same error. Removing the -i option does not constitute a standalone backend. See http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-reindex.html for a detailed description of the procedure you need to follow here. (7.3 doesn't require this pushup anymore for toast tables, btw.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] CREATE table1 FROM table2
Hi, How I create table1 from other table2 . "cp table1 table2" Thanks. -- Rado Petrik <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CREATE table1 FROM table2
Hi, On 17 Jun 2003, Rado Petrik wrote: > How I create table1 from other table2 . CREATE TABLE table1 AS SELECT * FROM table2; will work. Regards, -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.tdmsoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] CREATE table1 FROM table2
On 17 Jun 2003, Rado Petrik wrote: > Hi, > > How I create table1 from other table2 . > > "cp table1 table2" create table table2 as select * from table1; > > Thanks. > > -- == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-210-8981112 fax:+30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CREATE table1 FROM table2
Hi,
Documentation says:
CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [,
...] ) ]
AS query
example : create table table1 as select * from table2;
> -Message d'origine-
> De : [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] la part de Rado Petrik
> Envoyé : mardi 17 juin 2003 17:21
> À : [EMAIL PROTECTED]
> Objet : [SQL] CREATE table1 FROM table2
>
>
> Hi,
>
> How I create table1 from other table2 .
>
> "cp table1 table2"
>
> Thanks.
>
> --
> Rado Petrik <[EMAIL PROTECTED]>
>
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] CREATE table1 FROM table2
Dnia 2003-06-17 17:20, Użytkownik Rado Petrik napisał: Hi, How I create table1 from other table2 . "cp table1 table2" create table2 as select * from table1; It is described in Postgresql documentation SQL Commands -> "create table as" Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Catching DML exceptions in PL/pgSQL
Radu, > In any event, like I said, my java code should work with the functions as > they are now, whether db throws an exception or returns some result. > Also, the oracle-like exception handling would be really great and i know > i'm looking forward to it ! > Maybe in 7.5 ? Unfortunately, we seem to still lack a dedicated PL/pgSQL source developer on the project. Know anybody? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Object-Relational table design question
Hello everyone. I'm re-designing an ISP billing / customer tracking system, and I am confused a bit about object-relational tables. PostgreSQL is the first ORDBMS I've used and, though I've been using it for about two years now, I have never taken advantage of it's inheritance functionality. (BTW, if this isn't the correct forum to post this in, please let me know.) I'm trying to represent a set of services as a series of database tables; all service "classes" will have similar data -- base price, setup price, name, product code, etc -- but some will have more specific information. For instance, a dial-up account may be restricted by the number of hours available, and then there may be an additional fee for overage. A website account may be limited to disk space, monthly bandwidth quotas, etc. I thought of defining the different services in their tables, all inherited from the base "Service" table, and then insert rows for the different services of each (for instance "Basic Webhosting", "Advanced Webhosting", etc). I'm uncertain how much mileage I'll get with this approach however. When querying for all services a customer is subscribed to, would I be able to have it return -- on a row-by-row basis -- the separate columns of each sub-table even if that row isn't available for another record? (sort of like a left outer join would be; or would I be better off just doing a plain-ol' left outer join across all my inherited service tables?) Thanks in advance. I'd appreciate any feedback you have to offer. -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: 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: [SQL] Object-Relational table design question
Michael, > (BTW, if this isn't the correct forum to post this in, please let me know.) This is the right forum. > I thought of defining the different services in their tables, all inherited > from the base "Service" table, and then insert rows for the different > services of each (for instance "Basic Webhosting", "Advanced Webhosting", > etc). I'm uncertain how much mileage I'll get with this approach however. > > When querying for all services a customer is subscribed to, would I be able > to have it return -- on a row-by-row basis -- the separate columns of each > sub-table even if that row isn't available for another record? (sort of > like a left outer join would be; or would I be better off just doing a > plain-ol' left outer join across all my inherited service tables?) Personally, I'm not a fan of inherited tables; I think they muddy up the relationality of SQL without providing any additional functionality. However, my opinion is (obviously) not shared by everyone. Were I building your database, I would instead do it "relationally" rather than using inheritance (hopefully the ASCII art will transmit ...) Customer (name, id, phone, etc.) | one | V many Service (type, price, payment method, sign-up date, etc.) | || V V V Webhosting Dial-up DSL DetailsDetails Details | V Advanced Details This sort of partitioning of data is what the relational model is *for*; using the above, you should be able to access as much, or as little, of each customer's service details as you wish with left outer joins or by sub-selecting on service type. For example, to get webhosting details: SELECT ... FROM customer JOIN service ON (customer.id = service.customer AND service.type = 'web') JOIN webhosting ON service.id = webhosting.service LEFT OUTER JOIN web_advanced ON webhosting.id = web_advanced.webhosting Which would give you all customer, service, and basic hosting details, plus advanced hosting details of there are any. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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: [SQL] CREATE table1 FROM table2
Hi, Le Mardi 17 Juin 2003 20:46, Achilleus Mantzios a écrit : > On 17 Jun 2003, Rado Petrik wrote: > > How I create table1 from other table2 . > > > > "cp table1 table2" > > create table table2 as select * from table1; > Another way would be: select * into table2 from table1; (See http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-selectinto.html). -- Guillaume . ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] disabling triggers
> Mallah, > >> Is setting reltriggers=0 on pg_class an accepatble way of >> disabling triggers on a table temporarily? Ok , but someone on list was scary few months back. > > Yes. pg_dump does this. > >> secondly is it worth having commands like >> alter trigger inactive; >> alter trigger active ; >> i feel such commands exists on Oracle. > > No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres. Agreed , but there is no easy way to view current trigger defination of existing triggers before dropping from psql . One has to go the long way of pg_dump -s -t tabname to view a list. regds mallah. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] disabling triggers
Mallah, > Is setting reltriggers=0 on pg_class an accepatble way of > disabling triggers on a table temporarily? Yes. pg_dump does this. > secondly is it worth having commands like > alter trigger inactive; > alter trigger active ; > i feel such commands exists on Oracle. No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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: [SQL] disabling triggers
> Mallah, > >> >> Is setting reltriggers=0 on pg_class an accepatble way of >> >> disabling triggers on a table temporarily? >> >> Ok , but someone on list was scary few months back. > > I've done it many times without a problem. The trick is re-setting the triggers to > the > correct number when you're done. See the scripts that pg_dump -a creates for a > good example. > >> Agreed , but there is no easy way to view current trigger defination of existing >> triggers >> before dropping from psql . > > So? Create a perl script. It can: > 1) query the system tables to find out the trigger definintion > (you'll need pg_trigger, pg_proc, and pg_type) > 2) generate a script to restore all the triggers to be used later; 3) drop all the > triggers > > Of course, setting reltriggers=0 is probably a lot easier. in past we tried creating a plpgsql function that takes relname and schema and try setting reltriggers=0 on disabling and = (select count(*) from pg_trigger for that relname and schema) on enabling . we got stuck somewhere and abandoned it , do u think above approach would work in principle ? i will retry it tommorw though and post the results. regds mallah. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- TIP 3: 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 - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 3: 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: [SQL] join syntax
Jodi, > One of my colleagues has created a database where he has the same field > name in two tables and uses this field to link his tables rather than > some arbitrary value. For example, he has used "exp_id" in two tables. > When writing his joins he uses a syntax that says something like JOIN ON > EXP_ID. Can someone tell me what that syntax should be? I am not very > familiar with it since I typically use the syntax where one field is set > equal to the other. table_a JOIN table_b USING (exp_id) > Personally I prefer not to set databases up this way but cannot seem to > convince him of this. And yet I am supposed to now help him with his > database and application.. That, we can't help you with. Point out to your friend, though, that an explicit JOIN order can often harm performance when the JOIN order given by the query writer is not ideal. That is, the "FROM table, table, table WHERE expression, expression" syntax gives the parser a freer hand to choose the fastest execution method. Of course, on a very small database that typically makes litte difference. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] disabling triggers
Mallah, > in past we tried creating a plpgsql function that takes relname and schema > and try setting reltriggers=0 on disabling and = (select count(*) from pg_trigger > for that relname and schema) on enabling . > > we got stuck somewhere and abandoned it , do u think above approach > would work in principle ? Yes. As I said, I've used it before. An additional safeguard you can use is enclosing everything in a transaction, that is: BEGIN disable triggers load data enable triggers END -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] join syntax
On Tue, 17 Jun 2003, Jodi Kanter wrote: > One of my colleagues has created a database where he has the same field > name in two tables and uses this field to link his tables rather than > some arbitrary value. For example, he has used "exp_id" in two tables. > When writing his joins he uses a syntax that says something like JOIN ON > EXP_ID. Can someone tell me what that syntax should be? I am not very > familiar with it since I typically use the syntax where one field is set > equal to the other. > Personally I prefer not to set databases up this way but cannot seem to > convince him of this. And yet I am supposed to now help him with his > database and application.. Is there some documentation that would define > this type of syntax? How is this handled if you have more than one table > in the join? It does not appear that this format would allow for this. If you're joining two tables on a field that has the same name you can use a natural join. select * from table1 natural join table2; ---(end of broadcast)--- TIP 3: 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: [SQL] Catching DML exceptions in PL/pgSQL
At 6/17/2003 08:33, Josh Berkus wrote: Radu, > In any event, like I said, my java code should work with the functions as > they are now, whether db throws an exception or returns some result. > Also, the oracle-like exception handling would be really great and i know > i'm looking forward to it ! > Maybe in 7.5 ? Unfortunately, we seem to still lack a dedicated PL/pgSQL source developer on the project. Know anybody? Unfortunatelly yacc and myself are not good friends, at least not yet :-) Aside from that, this is pretty bad news for me :-( PEOPLE HELP OUT PL/pgSQL !!! :-O Cheers, -- Radu-Adrian Popescu CSA, DBA, Developer Aldratech Ltd. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] join syntax
Title: One of my colleagues has created a database where he has the same field name in two tables and uses this field to link his tables rather than some arbitrary value. For example, he has used "exp_id" in two tables. When writing his joins he uses a syntax that says something like JOIN ON EXP_ID. Can someone tell me what that syntax should be? I am not very familiar with it since I typically use the syntax where one field is set equal to the other. Personally I prefer not to set databases up this way but cannot seem to convince him of this. And yet I am supposed to now help him with his database and application.. Is there some documentation that would define this type of syntax? How is this handled if you have more than one table in the join? It does not appear that this format would allow for this. Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [SQL] yet pg_toast reindex
Sorry about this sucks thread but now i´v got following error ERROR: relation "pg_toast_26474986" is of type "t" I looked for "type t" around documentation and don´t find something usefull. tnx Eric Anderson Martins Miranda Net Admin @ Via Net SAO - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, June 17, 2003 11:55 AM Subject: Re: [SQL] yet pg_toast reindex > "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > > When I try to reindex a toast table with command ´REINDEX TABLE > > pg_toast_16557' I receive following error message: > > ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone > > postgres with -O -P options > > So I restart postmaster standalone (no -i option) and I receive same error. > > Removing the -i option does not constitute a standalone backend. See > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-reindex. html > for a detailed description of the procedure you need to follow here. > > (7.3 doesn't require this pushup anymore for toast tables, btw.) > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: 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: [SQL] [ADMIN] Notification
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 17 June 2003 05:13, Anagha Joshi wrote: > Hi All, > I'm new to Postgres. I'm using Postgres-7.2.4 > Can anybody guide me in detail how 'NOTIFY-LISTEN' can be implemented > progmatically bet'n frontend and backend? > Might want to move this to the SQL list. The idea is that a process will LISTEN for some notification. Another process will NOTIFY that notification, and the one listening will get a message. This is useful for a couple of things. If you have an app that shows something like customer data, then you might want to update the customer info when it changes. Here's the table: CREATE TABLE customer ( customer_id SERIAL PRIMARY KEY , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL ); When displaying the customer info for customer_id 19291, you could do something like: LISTEN customer_19291; Now when someone else goes in and changes the customer info in the database, you can have a trigger set up that will NOTIFY. CREATE OR REPLACE FUNCTION notify_on_update() RETURNS TRIGGER AS ' BEGIN EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.customer_id; RETURN NEW; END ' LANGUAGE 'plpgsql'; CREATE TRIGGER notify_on_update AFTER update ON customer FOR EACH ROW EXECUTE PROCEDURE notify_on_update(); Now you set up your application to watch for the notification. When it receives the notification, if can update the displayed information. Is this a good starting place? - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+73tPWgwF3QvpWNwRAqSQAKC/IsVy45bg4FAy4vwOK0PvBcfbjACg2XWT D98xYLKLXVuQPkUGTiCVHVE= =kpg2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [HACKERS] Our FLOAT(p) precision does not conform to spec
"Shridhar Daithankar" <[EMAIL PROTECTED]> writes: > On 16 Jun 2003 at 18:15, Tom Lane wrote: >> This is a straightforward change and would not break pg_dump files, >> since fortunately pg_dump always references the underlying types and >> never refers to anything as FLOAT(p). But I wonder whether it is >> likely to break many existing applications. There is a hazard of some >> existing app asking for (what it thinks is) float8 and getting float4 >> instead. > I hate the syntax of putting decimal digits as range checkers in SQL > field. But oracle does that and consequently lot of oracle apps rely > on it. I won't be surprised if float(p) notion brings same assurance > to such app developers. You are confusing NUMERIC --- which does allow exact precision limits to be specified --- with FLOAT, which does no such thing. It has never been the case in Postgres that FLOAT(p) would restrict you to exactly p digits. The underlying implementation is that there are just two kinds of float (single and double precision) and you get whichever can hold at least p digits. This is per spec, which states that you get at least p digits, not exactly p digits. Our only problem is that whoever wrote that code failed to notice that p is supposed to be measured differently for FLOAT than for NUMERIC. AFAICT, other databases get this right (at least Oracle and DB2 do), so expectations of developers are more likely to be that we conform to the spec than that we don't. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [HACKERS] Our FLOAT(p) precision does not conform to spec
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Considering that the data type float(x) isn't documented anywhere, I'm not > worried. Good point ... I'll fix that while I'm at it ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Blobs with perl
Hi, For not so big applications you could consider converting the binary image/file into text, using base64, then store the resulting text. For example I have an app. where each member may have one or two images on their account. So when the user upload's their image I base64 it and store the text. Then when we need to see the image select the text and decode it back to binary format. It work's well for me. This way only text is stored. It's also handy as you don't need to worry about file name's being the same. It will increase the file size by about 30%. Search the web for base64 example's, I'm sure you'll find examples to work with. You can email me off list if you like to see some PHP code which does this. Cheers Rudi. Jonathan Gardner wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 17 June 2003 02:00, cristi wrote: I want to insert a picture in a table from an internet browser using a script made in perl. Has somebody a code example with this kind a problem (I need only a code fragment)? Despite PostgreSQL's powerful BLOB features, I would strongly suggest against storing these kind of things in the database. It is better to have it in a local file for several reasons. 1) Apache can server up local images lightning fast 2) You can edit local images with your favorite image editor (ie, Gimp, Photoshop) 3) You can ftp, scp, sftp the image around without a problem. 4) You can tar it up and archive it. 5) You can move it off of your burdened PostgreSQL database server machine and on to its own image server when your site becomes popular. While all of the above are certainly possible with PostgreSQL, it is a bit more complicated. And remember, while your database server is small now, it will grow, and grow, and grow, and grow. It will one day become the bottleneck in your operations. That is an inevitable fact of any dynamic website. - -- Jonathan Gardner <[EMAIL PROTECTED]> (was [EMAIL PROTECTED]) Live Free, Use Linux! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+7yYTWgwF3QvpWNwRAv3WAKDFrjfQUpQFmZFvVMismUeoxABoDQCfY/F7 LajclhvacQOgsn+6qnLEEwQ= =k0vW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Blobs with perl
On Tuesday 17 June 2003 02:00 am, cristi wrote: > I want to insert a picture in a table from an internet browser using a > script made in perl. > Has somebody a code example with this kind a problem (I need only a code > fragment)? I put album covers into a database table, primarily because I wanted them closely tied to database data without having any dependancy on a specific filesystem structure. Anyway, performance isn't much to shout about, but here's the relevant code snippet that I use to insert album images. It uses LWP::UserAgent to download the jpeg, and then plug it into my database table: my $insert_cover = $dbh->prepare(qq| UPDATE Album SET CoverLarge = ? , CoverMedium = ? , CoverSmall = ? WHERE ID = ? |); my $small_url = "$image_url?S=$image_pid&X=60&Y=60"; my $medium_url = "$image_url?S=$image_pid&X=120&Y=120"; my $large_url = "$image_url?S=$image_pid&X=178&Y=178"; return unless ($image_pid); #print "\$id = \"$id\"\n"; #print "\$small_url = \"$small_url\"\n"; #print "\$medium_url = \"$medium_url\"\n"; #print "\$large_url = \"$large_url\"\n"; my $small_image = $ua->get($small_url)->content; my $medium_image = $ua->get($medium_url)->content; my $large_image = $ua->get($large_url)->content; $insert_cover->bind_param(1, $large_image, DBI::SQL_BINARY); $insert_cover->bind_param(2, $medium_image, DBI::SQL_BINARY); $insert_cover->bind_param(3, $small_image, DBI::SQL_BINARY); $insert_cover->bind_param(4, $id); $insert_cover->execute; This comes from a throw-away script I whipped up to migrate from an older system, so the code isn't all that clean (e.g. not commented, convoluted variable names, etc) but it should get you started. -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [ADMIN] Can the frontend get notifications from the postgres server?
On Tue, Jun 17, 2003 at 01:37:37PM -0700, Jonathan Gardner wrote: > Why in the world would you want to? Think about this for a moment: > The script runs in under a second - why would it want to sit around > and wait for a notification? No, my script runs hours, days, month ... cu -- - Enrico Weigelt== metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 smsgate: [EMAIL PROTECTED] - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ ---(end of broadcast)--- TIP 3: 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: [SQL] yet pg_toast reindex
"Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > Sorry about this sucks thread but now i´v got following error > ERROR: relation "pg_toast_26474986" is of type "t" You may have to use REINDEX INDEX (on the index not the toast table of course) to get it to work in older versions. REINDEX didn't use to think it could work on toast tables. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] disabling triggers
Hi, Is setting reltriggers=0 on pg_class an accepatble way of disabling triggers on a table temporarily? secondly is it worth having commands like alter trigger inactive; alter trigger active ; i feel such commands exists on Oracle. sometime we need to bulk update tables from backdoor and we feel the trigger execution to be unneccessary. regds mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] date question
javier garcia - CEBAS writes: > Is there a way to create a table from this table, directly in Postgres in > which a date field is created based in the values of "year", "month", "day" > in this table? year * interval '1 year' + month * interval '1 month' + day * interval '1 day' This results in a timestamp value that you can compare to or assign to a date value. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] disabling triggers
Mallah, > >> Is setting reltriggers=0 on pg_class an accepatble way of > >> disabling triggers on a table temporarily? > > Ok , but someone on list was scary few months back. I've done it many times without a problem. The trick is re-setting the triggers to the correct number when you're done. See the scripts that pg_dump -a creates for a good example. > Agreed , but there is no easy way to view current trigger defination of > existing triggers before dropping from psql . So? Create a perl script. It can: 1) query the system tables to find out the trigger definintion (you'll need pg_trigger, pg_proc, and pg_type) 2) generate a script to restore all the triggers to be used later; 3) drop all the triggers Of course, setting reltriggers=0 is probably a lot easier. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: 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: [SQL] [HACKERS] Our FLOAT(p) precision does not conform to spec
Tom Lane writes: > This is a straightforward change and would not break pg_dump files, > since fortunately pg_dump always references the underlying types and > never refers to anything as FLOAT(p). But I wonder whether it is > likely to break many existing applications. There is a hazard of some > existing app asking for (what it thinks is) float8 and getting float4 > instead. Considering that the data type float(x) isn't documented anywhere, I'm not worried. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
