Re: [GENERAL] problem with serial data type and access
Adrian Klaver ha scritto: The fact is that the serial data type is in pratice an integer, and when I also try to connect with pgadminIII I see an integer data type and not a serial. I think that since it sees an integer, it does not understand that it is a serial, and access does not recognize it as autoincrement. I'm stuck at this point... Did you mark this field as the primary key when you linked the table? yes I did -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] problem with serial data type and access
Scott Marlowe ha scritto: On Thu, May 15, 2008 at 7:54 AM, Ottavio Campana <[EMAIL PROTECTED]> wrote: I know it's not fully IT with the list, but maybe somebody can help me. I'm dealing with this scenario: access 97 is connected through odbc to a postgresql server. All tables are saved in postgresql and access is used only to generated the program interface. Everything works fines, but I'm having problems with the serial data type. I know a serial is an integer having as default the next value of a sequence. Since it is an integer, access does not recognize it as an autoincrement value, and it asks for is value. Did anyone of you already have this problem? Can you feed it a value of DEFAULT??? I'd like to, but I don't think I could do it. The fact is that the serial data type is in pratice an integer, and when I also try to connect with pgadminIII I see an integer data type and not a serial. I think that since it sees an integer, it does not understand that it is a serial, and access does not recognize it as autoincrement. I'm stuck at this point... -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] problem with serial data type and access
I know it's not fully IT with the list, but maybe somebody can help me. I'm dealing with this scenario: access 97 is connected through odbc to a postgresql server. All tables are saved in postgresql and access is used only to generated the program interface. Everything works fines, but I'm having problems with the serial data type. I know a serial is an integer having as default the next value of a sequence. Since it is an integer, access does not recognize it as an autoincrement value, and it asks for is value. Did anyone of you already have this problem? Thanks... -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] question about join
Osvaldo Kussama ha scritto: To further explain, the following query selects both the rows from the join where id_ref_first_tab has the desired value and default_value = true, while I want to select the row corresponding to default_value = true only in case no row corresponding to id_ref_first_tab exists. select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or default_value = true; I hope I've been clear enough... Try: select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = true); it's not what I want, because it can return two rows, while I want only one row back, checking the first condition and optionally the second one only if the first one is not matched. I don't know if it is possible, but if it could, it would be great. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] question about join
Hi, I'm having a problem trying to write a query using join, and I hope you can give me a hint. suppose you have a three tables like these: create table first_table ( id serial primary key, description1 text); create table second_table ( id serial primary key, description2 text); create table third_table ( id serial primary key, description3 text, id_ref_first_tab integer references first_table(id), id_ref_second_tab integer references second_table(id), default_value boolean); create unique index idx1 on third_table (id_ref_first_tab,id_ref_second_tab); create unique index idx2 on third_table (id_ref_second_tab) where default_value = true; What I'm trying to do is joining the second and the third tables on second_table.id = third_table.id_ref_second_tab to extract all the values in third_table where id_ref_first_tab has a given value or, in case it is not present, to extract only row that has default_values = true; To further explain, the following query selects both the rows from the join where id_ref_first_tab has the desired value and default_value = true, while I want to select the row corresponding to default_value = true only in case no row corresponding to id_ref_first_tab exists. select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or default_value = true; I hope I've been clear enough... Thanks in advance, Ottavio signature.asc Description: OpenPGP digital signature
[GENERAL] problem with tsearch and utf-8 on postgresql 8.1
I created a database with locale [EMAIL PROTECTED], and I'm facing a weird
problem with tsearch. Let me show it:
tmptest=# SELECT * from pg_ts_cfg;
ts_name | prs_name |locale
-+--+--
default_russian | default | ru_RU.KOI8-R
simple | default |
default | default | [EMAIL PROTECTED]
(3 righe)
tmptest=# SELECT to_tsvector ('ciao mamma');
ERROR: could not find tsearch config by locale
tmptest=# UPDATE pg_ts_cfg set locale = '[EMAIL PROTECTED]' where ts_name =
'default';
UPDATE 1
tmptest=# SELECT to_tsvector ('ciao mamma');
to_tsvector
'ciao':1 'mamma':2
(1 riga)
tmptest=# UPDATE pg_ts_cfg set locale = '[EMAIL PROTECTED]' where ts_name =
'default';
UPDATE 1
tmptest=# SELECT to_tsvector ('ciao mamma');
to_tsvector
'ciao':1 'mamma':2
(1 riga)
tmptest=# \q
So at first tsearch does not work. If I change it to [EMAIL PROTECTED] it works
and if I switch back to [EMAIL PROTECTED] it then works even with that locale.
I cannot understand the reason why it happens. By disconnecting from the
db and connecting again the problem is still there. Do you have any idea
why it happens?
I don't know if it matters, on the systems I have LANG="[EMAIL PROTECTED]" and
LANGUAGE="it_IT"
Thanks,
Ottavio
PS: Im running a backport of postgresql 8.1 on debian sarge.
--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.
signature.asc
Description: OpenPGP digital signature
[GENERAL] executing a procedure with delay
I'd like to execute a stored procedure in postgresql one minute after a table has been modified. In case there are two changes in less than one minute, I want to reset the time that has to be waited before running the procedure. I think I need to use a trigger, but I don't know how... Can you help me please? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Read-only availability of a standby server?
Stefan Kaltenbrunner ha scritto: > Garber, Mikhail wrote: >> In the high-availabilty situation with a warm standby, is it possible (or >> planned) to be able to make standby readable? >> This is a new feature in Oracle 11 and it is very important for a project I >> am working on. > > yeah there are plans to support this in 8.4 (and some basic groundwork > already happened in 8.3) ... cool! do you have some links to pages? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] maximum size of plpgsql function parameter
I want to pass some text to a function which is going to store it for some tsearch queries. Is there a limit on the length of the text I can pass to a function? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] stripping HTML, SQL injections ...
Alvaro Herrera ha scritto: > Martin Gainty escribió: >> this is a very simple html tag strip routine >> I dont understand what security you had in mind .. >> >> so I take it you're not a fan of dojo or GWT? > > Let's say the user disables javascript on the browser? or more easily, an attacker can use the firefox web developer toolbar to manipulate forms data... -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] create visual query in web applications
Do you know any library or application so that a user could create visually a query in a web application? I think that now with ajax and web 2.0 it should be possible, but I don't know any product that does it. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] question about pg_dump -a
Vivek Khera ha scritto: > > On Sep 28, 2007, at 9:07 AM, Ottavio Campana wrote: > >> But why does pg_dump does not already exports data such that previous >> tables do not depend on successive ones? > > Because you can't always sort your tables that way. The restore > procedure is responsible for either sorting or disabling the FK checks > during bulk load. The latter is more efficient, especially if there are > no indexes yet, as in a full restore from dump. how can FK checks be disabled? is there a command? signature.asc Description: OpenPGP digital signature
Re: [GENERAL] question about pg_dump -a
Richard Huxton ha scritto: > Ottavio Campana wrote: >> >> Is there a way to export tables in order, so that dependencies are >> always met? reading the manpage of pg_dump I found the -Fc flag, but I >> haven't understood if it is good for me and how it works. Or is there a >> way to relax constraints while loading data? > > Try a pg_dump with -Fc and then pg_restore --data-only. > > If all else fails, you can control item-by-item what gets restored by > producing a list from pg_restore (--list), commenting out lines and then > using it as a specification with (--use-list). See manuals for full > details. with -L I was able to solve it, thanks. But why does pg_dump does not already exports data such that previous tables do not depend on successive ones? signature.asc Description: OpenPGP digital signature
[GENERAL] question about pg_dump -a
I have a database which I create using dia and tedia2sql. I developed another version with more tables, without changing anything that was already present in the first version. Now I want to copy the data from one database to another, so I thought about pg_dump -a, assuming that since there is no change in the structure and I can freely and and reload the information. My problem is that when I reload the data into the new database, I have several error about foreign keys violation. For what I've been able to understand, it seems to be a problem of loading order and tables referring to others are loaded earlier than those. Is there a way to export tables in order, so that dependencies are always met? reading the manpage of pg_dump I found the -Fc flag, but I haven't understood if it is good for me and how it works. Or is there a way to relax constraints while loading data? signature.asc Description: OpenPGP digital signature
Re: [GENERAL] "not in" clause too slow?
Alban Hertroys ha scritto: > Ottavio Campana wrote: >> 2) how can I speed it up? by using indexes? or by changing the query? > > Do you have indices on mytable.id and copy_mytable.id? > Does using NOT EXISTS get you any better results? Eventually I had to select not all the table fields but only the primary key and successively loop on the table again. Thus I've been able to exploit the indexes. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] "not in" clause too slow?
Alban Hertroys ha scritto: > Ottavio Campana wrote: >> 2) how can I speed it up? by using indexes? or by changing the query? > > Do you have indices on mytable.id and copy_mytable.id? > Does using NOT EXISTS get you any better results? mytable.id is primary key. I create copy_mytable with create table copy_mytable as (select * from mytable); the planer behavior does not change no matter if I create and index on copy_mytable.id or not. >> db=# EXPLAIN ANALYZE select * from mytable where id not in (select id >> from copy_mytable); >> QUERY PLAN >> >> -- >> Seq Scan on mytable (cost=53.21..148.34 rows=925 width=96) (actual >> time=9.813..9.813 rows=0 loops=1) >>Filter: (NOT (hashed subplan)) >>SubPlan >> -> Seq Scan on copy_mytable (cost=0.00..48.57 rows=1857 width=4) >> (actual time=0.031..3.132 rows=1857 loops=1) >> Total runtime: 10.291 ms >> > > signature.asc Description: OpenPGP digital signature
[GENERAL] "not in" clause too slow?
mytable has 1857 rows, copy_mytable is a copy of mytable and I want to know which new rows have been entered. I used the where id not in, and the query works. My problem is that if I run the same command on another table with 378415 rows, it is terribly slow. I ran explain analyze on the first table, just to see how is works, and I have two questions: 1) from explain analyze output, does the planner scan mytable and for each element runs a sec sqn on copy_mytable? 2) how can I speed it up? by using indexes? or by changing the query? db=# EXPLAIN ANALYZE select * from mytable where id not in (select id from copy_mytable); QUERY PLAN -- Seq Scan on mytable (cost=53.21..148.34 rows=925 width=96) (actual time=9.813..9.813 rows=0 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on copy_mytable (cost=0.00..48.57 rows=1857 width=4) (actual time=0.031..3.132 rows=1857 loops=1) Total runtime: 10.291 ms signature.asc Description: OpenPGP digital signature
Re: [GENERAL] queston about locking
Albe Laurenz ha scritto: > Ottavio Campana wrote: >> I'm writing a python script to update some tables in a db. My >> problem is >> that I need to lock a couple of tables, perform several operations and >> read the corresponding output. >> >> I was thinking about lock in exclusive mode, but in the >> documentation I >> found that it is valid only in a transaction. But since I need to >> execute a command and read the output and so forth, I think I >> cannot use >> a transaction. >> >> What would you use to lock the table? > > What makes you think that you "need to lock a couple of tables"? the point is that for each table I have a copy I previously made and I want to create an incremental backup. My problem is that I don't want the original table to change, so I lock it. I admin that exclusive lock is probably too much. Does share mode block inser/update/delete but allows reading? Thanks. PS: By the way, I just discovered that with python psycopg2 the cursor is wrapped in a transaction, so locking works. So the problem is only the correct lock level. signature.asc Description: OpenPGP digital signature
[GENERAL] queston about locking
I'm writing a python script to update some tables in a db. My problem is that I need to lock a couple of tables, perform several operations and read the corresponding output. I was thinking about lock in exclusive mode, but in the documentation I found that it is valid only in a transaction. But since I need to execute a command and read the output and so forth, I think I cannot use a transaction. What would you use to lock the table? signature.asc Description: OpenPGP digital signature
Re: [GENERAL] get a list of table modifications in a day?
hubert depesz lubaczewski ha scritto: > On Thu, Sep 13, 2007 at 09:59:30AM +0200, Ottavio Campana wrote: >> 1) pg_dump each day and run diff > > it will become increasingly painful as the table size increases. > >> 2) modify some triggers we use and store the information in another table > > this is the best choice. you can use table_log extension to avoid > writing your own triggers. > > http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html since I already use triggers on that table, can I use table_log? I mean, can I have two triggers for the same event on the same table? signature.asc Description: OpenPGP digital signature
[GENERAL] get a list of table modifications in a day?
I need to generate a diff (or something similar) of a table, day by day. What is the best way to tack insert/update/delete operations? I have two ideas, and I'd like to hear your opinion: 1) pg_dump each day and run diff 2) modify some triggers we use and store the information in another table I am not aware of any functionality offered by postgresql. Does it exists? If not, which solution would you prefer? signature.asc Description: OpenPGP digital signature
[GENERAL] do you have an easy example of postgis and mapserver?
Hi, I'm sorry this mail is not very in topic, but I hope you can help me. I'm trying to learn how postgis and mapserver work together, but I cannot understand nearly anything. I mean, I read the documentation of postgis and I think I understood it, but I cannot do anything useful with it. What I'd like to do is storing some polygons in a database and writing some scripts in python which extract those polygons and draw an image, given a zoom level and a position. Do you know some good documentation? Or a working example would be great. Thank you. signature.asc Description: OpenPGP digital signature
[GENERAL] memory leakage in libpg?
I'm developing a program in C that acquires data from an I/O card and stores values in postgresql. I noticed that the program uses more and more ram, so I decided to debug it with valgrind, and I found ==28449== 156 (36 direct, 120 indirect) bytes in 1 blocks are definitely lost in loss record 2 of 8 ==28449==at 0x402137E: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==28449==by 0x4154799: (within /lib/libc-2.5.so) ==28449==by 0x4154E85: __nss_database_lookup (in /lib/libc-2.5.so) ==28449==by 0x4459079: ??? ==28449==by 0x4459DAA: ??? ==28449==by 0x4112294: getpwuid_r (in /lib/libc-2.5.so) ==28449==by 0x4111C98: getpwuid (in /lib/libc-2.5.so) ==28449==by 0x4082A63: (within /usr/lib/postgresql-8.2/lib/libpq.so.5.0) ==28449== 40 bytes in 5 blocks are indirectly lost in loss record 3 of 8 ==28449==at 0x402137E: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==28449==by 0x41543BB: __nss_lookup_function (in /lib/libc-2.5.so) ==28449==by 0x4459099: ??? ==28449==by 0x4459DAA: ??? ==28449==by 0x4112294: getpwuid_r (in /lib/libc-2.5.so) ==28449==by 0x4111C98: getpwuid (in /lib/libc-2.5.so) ==28449==by 0x4082A63: (within /usr/lib/postgresql-8.2/lib/libpq.so.5.0) ==28449== 80 bytes in 5 blocks are indirectly lost in loss record 6 of 8 ==28449==at 0x402137E: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==28449==by 0x4144176: tsearch (in /lib/libc-2.5.so) ==28449==by 0x415437D: __nss_lookup_function (in /lib/libc-2.5.so) ==28449==by 0x4459099: ??? ==28449==by 0x4459DAA: ??? ==28449==by 0x4112294: getpwuid_r (in /lib/libc-2.5.so) ==28449==by 0x4111C98: getpwuid (in /lib/libc-2.5.so) ==28449==by 0x4082A63: (within /usr/lib/postgresql-8.2/lib/libpq.so.5.0) Do you have any hint to better identify the problem? Server and client are running gentoo 2007.0 x86 stable using CFLAGS="-march=i686 -mmmx -msse -msse2 -msse3 -Os -pipe -fomit-frame-pointer" -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] about cursors
Martijn van Oosterhout wrote: >> One last question: what happens to unclosed cursors? I mean, suppose an >> application opens a cursor and crashes. What happens to that cursor? Is >> there a way to close idle cursors? > > Cursors are attached to the transactio and session, if either ends, the > cursor dies with it... > > Have a nice day, another question: since they live in a transaction, how can they be used in web apps? Suppose you want to display only a subset of records a time in a page, each time you load a page you have to start a new transaction and therefore you need a new cursor, or not? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] about cursors
I never used cursors before, and I'm trying to understand how to use them well. Postgresql doc says "a cursor that encapsulates the query, and then read the query result a few rows at a time." So, when I open a cursor, is all the query executed and results are returned a few a time? My doubt comes from http://archives.postgresql.org/pgsql-sql/2005-08/msg00230.php where I read "when you open a cursor PostgreSQL doesn't know how many rows it will return". So I start thinking that maybe it does not execute the whole query At this point I'm not able to understand any more if cursor are useful to reduce computational needs compared to running the same query each time with limit and offset. One last question: what happens to unclosed cursors? I mean, suppose an application opens a cursor and crashes. What happens to that cursor? Is there a way to close idle cursors? Thanks. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] automatically execute a function each day
I want to execute a function automatically every day, let's say at midnight. Can I do it directly in postgresql, or do I have to use some external programs (cron?) ? Thanks. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] shut down one database?
Joshua D. Drake wrote: > Ottavio Campana wrote: >> Bill Moran wrote: >>> Ottavio Campana <[EMAIL PROTECTED]> wrote: >>>> I have postgresql running several databases. I can stop them all by >>>> stopping postgresql, but sometimes I'd like to shut down a single >>>> database. >>>> >>>> Can I get the same effect of stopping postgresql for only one database? >>> You can tweak pg_hba.conf to disallow all access to that particular >>> database. Will that accomplish what you want? >> >> well, that would be only a part: I'd also like to stop all the processes >> related to that db. Sometimes I have some connection I'd like to close... > > set datallowconn to false in pg_database; does it shut down the already existing ones? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] shut down one database?
Bill Moran wrote: > Ottavio Campana <[EMAIL PROTECTED]> wrote: >> I have postgresql running several databases. I can stop them all by >> stopping postgresql, but sometimes I'd like to shut down a single database. >> >> Can I get the same effect of stopping postgresql for only one database? > > You can tweak pg_hba.conf to disallow all access to that particular > database. Will that accomplish what you want? well, that would be only a part: I'd also like to stop all the processes related to that db. Sometimes I have some connection I'd like to close... -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] shut down one database?
I have postgresql running several databases. I can stop them all by stopping postgresql, but sometimes I'd like to shut down a single database. Can I get the same effect of stopping postgresql for only one database? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] tokenize string for tsearch?
Magnus Hagander wrote: >> I'm using 8.1.8 and I don't find plainto_tsquery in tsearch2.sql >> >> What can I do? > > Yeah, you need 8.2 for that function. I don't think anybody has tried > backpatching it, but if you want to you can look at the code in 8.2 and > see if you can backpatch it yourself. But the easiest way is certainly > to upgrade to 8.2. doh! that's not possible. :-( I'm solving with a custom stored procedure. Thanks -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] tokenize string for tsearch?
Magnus Hagander wrote:
> On Mon, May 07, 2007 at 05:31:02PM -0700, Ottavio Campana wrote:
>> Hi, I'm trying to use tsearch2 for the first time and I'm having a
>> problem setting up a query
>>
>> If I execute
>>
>> SELECT * from test_table where ts_desc @@ to_tsquery ('hello&world');
>>
>> it works, but I'm having the problem that the string used for the query
>> is not 'hello&world' but 'hello world', Moreover, it can have an
>> arbitrary number of spaces between the words, so I cannot just
>> substitute the spaces with &, because 'hello&&world' gives error.
>>
>> What is the safest way transform a string into a list of words "anded"
>> together?
>
> Look at plainto_tsquery().
db=# SELECT plainto_tsquery('default', 'hello word');
ERROR: function plainto_tsquery("unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
I'm using 8.1.8 and I don't find plainto_tsquery in tsearch2.sql
What can I do?
Thank you.
--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.
signature.asc
Description: OpenPGP digital signature
[GENERAL] tokenize string for tsearch?
Hi, I'm trying to use tsearch2 for the first time and I'm having a
problem setting up a query
If I execute
SELECT * from test_table where ts_desc @@ to_tsquery ('hello&world');
it works, but I'm having the problem that the string used for the query
is not 'hello&world' but 'hello world', Moreover, it can have an
arbitrary number of spaces between the words, so I cannot just
substitute the spaces with &, because 'hello&&world' gives error.
What is the safest way transform a string into a list of works "anded"
together?
Thank you
--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.
signature.asc
Description: OpenPGP digital signature
[GENERAL] change the stop words file for tsearch2
How can I change the name of the file with the stop words used by tsearch2? I tried searching in the pg_ts_* tables, but I didn't find anything. Moreover, suppose you have a table with (text,ts_vector,boolean) columns. Do you think it might be possible to use two different files of stop words, in case the boolean field is true or false? Ciao -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] concurrency in stored procedures
Ottavio Campana wrote: > Anyway, apart from a couple of errors in the code i wrote (an in isn't > closed and exception handling is not correctly written), can I be sure > that the code in the sub-block works an a snapshot of the db? It seems not to work, I put it into my application and I got Error Type: ProgrammingError Error Value: ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query CONTEXT: SQL statement "set transaction isolation level serializable" PL/pgSQL function "test_function" line 31 at SQL statement select test_function ( 11, 'tizio', '', ' ', '', ' ', '', ' ', ' ', ' ', '', '', ' ', ' ', '0.0', ' ', '', 1, 1, 0.0, 1 ) as risultato; So I think I'll have to use locking -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] concurrency in stored procedures
Merlin Moncure wrote: > On 3/23/07, Ottavio Campana <[EMAIL PROTECTED]> wrote: >> Ottavio Campana wrote: >> > What would you to in order to be sure that one function or a part of it >> > is atomically executed? >> >> would it be correct something like? or how would you write this? >> >> create or replace function my_function () returs integer as >> $$ >> declare >> ... >> status boolean; >> ... >> begin >> ... >> loop >> begin >> set transaction isolation level serializable; >> ... >> do_something(); >> ... >> status := true; >> exception serialization_failure >> status := false; >> end; >> >> if status then exit; >> end loop; >> ... >> return 0; >> end >> $$ language plpgsql > > you can also use advisory locks if you want to implement 'critical > section' inside a plpgsql function. am I wrong or are advisory locks available only in 8.2? Anyway, apart from a couple of errors in the code i wrote (an in isn't closed and exception handling is not correctly written), can I be sure that the code in the sub-block works an a snapshot of the db? Thank you -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] concurrency in stored procedures
Ottavio Campana wrote: > What would you to in order to be sure that one function or a part of it > is atomically executed? would it be correct something like? or how would you write this? create or replace function my_function () returs integer as $$ declare ... status boolean; ... begin ... loop begin set transaction isolation level serializable; ... do_something(); ... status := true; exception serialization_failure status := false; end; if status then exit; end loop; ... return 0; end $$ language plpgsql -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] concurrency in stored procedures
Hi, using constraints on tables I was able to remove some race conditions, because the unique index prevents the same data to be inserted twice into the table. But I still didn't fix all the race conditions, because in some functions I have to modify more than one table or I just have read and write data in the same table. So, what is the best way to handle concurrency in stored procedures? I read that using locks isn't good because it may lead to deadlocks, so I was thinking about transactions, but I wan't able to find a good example. What would you to in order to be sure that one function or a part of it is atomically executed? I also read that postgresql is able to detect deadlocks and can try to solve them. How does this happen in a stored procedure and how can a procedure know that it was aborted because of the deadlock? Thank you -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] questions about query design
Hi, I'm trying to implement some stored procedures but I'm having some doubts, and I'd like to ask you if I'm doing well or not. Here's an example of what I'm doing: I have a table like create table ( id serial, description text not null, active boolean default true); What I want to do is a function inserting a new item into the table ensuring that there is only one record in the table having a particular description and at the same time the active field set to true (it might seem stupid, but the application requires it). My first solution was a function executing a select on the table checking for a record with the passed description and the active field set to true. If a record is found, then the function fails. This function works, but I don't think it's thread safe, since two functions could be executed at the same time, so that they pass the test and insert twice the record. To solve the problem, I tried to put a constraint on the table, but I didn't figure how to do it. How can I add the constraint "description is unique among all the record having active set to true"? I think that having this constraint would assure me that one of the two function will fail, so I'll be able to handle the exception. Am I right? I also have a second small question. In faq 4.11.3 they say that currval() doesn't lead to race conditions. How does it work? I can't really understand the meaning of "currval() returns the current value assigned by your session, not by all sessions". Thank you. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] blocking function in PL/Python
suppose we have a stored procedure written in PL/Python. What happens if the function blocks for a while? Does the server still works for the other clients? signature.asc Description: OpenPGP digital signature
Re: [GENERAL] more than 32 parameters to a function?
I just implemented the same function using an array holding all the booleans fields describing the objects. It works well. Thank you to all of you. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] more than 32 parameters to a function?
Erik Jones wrote: > Put them in arrays and pass them as the arguments. But, I would like to > know what function could ever need 65 arguments? Consider that I have to invoke the function from a ZSQL method in zope. Do you know if it works? I need all these arguments because we have a tables where we store items for an e-commerce site. The problem is that these items might have a lot of peculiarities and more than 40 fields are boolean to fully describe them. Bye > Ottavio Campana wrote: >> I'm writing some stored procedures in pl/pgsql for a database using >> postgresql 7.4.7. >> >> I need to write a complex function with 65 arguments, but when I try to >> run it I get an error complaining that arguments can be up to 32. >> >> Is there a way to solve this problem or do I have to try to split the >> function into three new ones? >> >> > > -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] more than 32 parameters to a function?
I'm writing some stored procedures in pl/pgsql for a database using postgresql 7.4.7. I need to write a complex function with 65 arguments, but when I try to run it I get an error complaining that arguments can be up to 32. Is there a way to solve this problem or do I have to try to split the function into three new ones? -- Non c'e' piu' forza nella normalita', c'e' solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Ajax/PostgreSQL
On Sat, Aug 05, 2006 at 08:27:25PM -0300, Jorge Godoy wrote: > Paul M Foster <[EMAIL PROTECTED]> writes: > > Here's an example: The user wants to enter a bill (accounts payable) > > into the system. He first has to pick a vendor. Normally, this would > > entail a PHP page that generates a PostgreSQL query. The user would > > then get a second page with various vendor information (like number > > of due days for that vendor), and various other payable info. > > But wouldn't it be nice to have vendor information filled in > > on the original page, directly after the user picks a vendor? > > Theoretically, AJAX might allow something like this. But from what I > > can see, it would require PostgreSQL bindings in Javascript, and > > some way to pass the data back so that PHP could use it. > > I'd do it the reverse: Javascript would call a PHP-enabled URL, PHP > would get the data, return to JS, JS would then populate the form. > > This way all your logic is contained within PG and PHP. JS would only > be used to manipulate the interface. I'm not using php, but the idea behind it is similar. I use postgresql, zope and scriptaculous. The autocompleter calls a ZSQL method, which calls a stored procedure and returns data to the ajax part of the website. If you give a look in google for scriptaculous examples, you'll find a lot of them for php working in a similar way: they call a php page which returns the information for the autocompleter. In all the examples I've seen there's no input sanitysing (they are all toy examples) but it is not difficult to implement it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] plpythonu and type record
I'm trying to write a stored procedure in python with postgresql 7.4, but I cannot return a record or a setof record. I get this error: ERROR: cannot accept a value of type record Is possible to return a record or am I trying to do something impossible? Thank you signature.asc Description: OpenPGP digital signature
[GENERAL] ean code data type
Is there a data type for ean codes for postgresql 7.4? I found the isbn data type, and I would appreciate something similar for ean codes. Thank you signature.asc Description: OpenPGP digital signature
[GENERAL] libpq for palm?
Is there a libpq for palm os? That would be great to develop applications. -- Non c'è più forza nella normalità, c'è solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] how to document database
Kaloyan Iliev ha scritto: > Hi, > > I am not familiar with doxygen, so I can't give you any advice. To me > postgresql_autodoc -d works perfectly. > I am useing version 1.25 of postgresql_autodoc. > > I recevice documentation of the sotred rocedures when I have comments on > them. Then when > postgresql_autodoc generate HTML documentation the comments are there. That's > it. > I am sorry if this doesn't help you much. so that must be a debian's bug. I'll work on it. -- Non c'è più forza nella normalità, c'è solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] how to document database
Kaloyan Iliev wrote: > Hi, > > I'm using postgresql_autodoc. It is perfect for me. And if you have > comments in the database the created document is like real documentation:-) I can't make it work. I'm running Debian etch, and I always get [EMAIL PROTECTED]:/tmp$ postgresql_autodoc -d tost Can't call method "finish" on an undefined value at /usr/bin/postgresql_autodoc line 1203. [EMAIL PROTECTED]:/tmp$ man postgresql_autodoc do you know what's wrong with it? And how do you document the stored procedures? can you have something similar to doxygen with postgresql_autodoc? -- Non c'è più forza nella normalità, c'è solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] how to document database
I need to document the database I develop so that other people can easily understand how it works. I particularly want to document the stored procedures. By now I've used a javadoc style to document them. I can't use tools like doxygen on them but it is always better than nothing. I'd like to know if you're using some particular tool. Thanks -- Non c'è più forza nella normalità, c'è solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] database design questions
hubert depesz lubaczewski wrote: > 2) do you think it's possible in a plpgsql procedure select the name of > a table into a variable and use that variable in the query? > possible, but not really good way. read about 'execute' in plpgsql. why isn't it good? I mean, from my point of view is like a function accepting a pointer. In many languages it is used. -- Non c'è più forza nella normalità, c'è solo monotonia. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] database design questions
Alban Hertroys wrote: > Ottavio Campana wrote: > >>> CREATE TABLE person ( >>> id SERIAL, >>> name TEXT >>> ); > > >> how can I do it with a INT8 instead of a INT4? > > > Do you really expect that sequence to reach over 2 billion? Otherwise > I'd stick with the SERIAL, nothing wrong with that unless you're selling > electrons seperately or something like that (hmm... how much are they? I > sure could use a few extra). I agree wih you, but I think that in the feature the could be more than 2 billions. I don't want to alter in the future the database -- Non c'è più forza nella normalità, c'è solo monotonia. signature.asc Description: OpenPGP digital signature
[GENERAL] database design questions
Hello,
I'm designing a database and I'm having some problems, so I ask you a
suggestion.
1) The database I'm going to develop is a big list with a catalog of
items and I want to store subsets of this list representing the
available items in several places.
My idea is to create the big table with all the elements and then to
create another table, where each row holds a pair (id_item, id_place)
and thanks to this create several views, joining the two tables
and selecting the rows with a give id_place.
Do you think it's too heavy? Is there a simpler way to do it?
2) do you think it's possible in a plpgsql procedure select the name of
a table into a variable and use that variable in the query?
I mean, can I do something like
SELECT INTO table_name get_table_name();
SELECT * FROM table_name;
?
3) faq 4.11.1 says
>CREATE TABLE person (
>id SERIAL,
>name TEXT
>);
>
>is automatically translated into this:
>
>CREATE SEQUENCE person_id_seq;
>CREATE TABLE person (
>id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
>name TEXT
>);
how can I do it with a INT8 instead of a INT4?
Thank you
--
Non c'è più forza nella normalità, c'è solo monotonia.
signature.asc
Description: OpenPGP digital signature
