[SQL] arrays in PostgreSQL
> Is it possible to return an array from a plpgsql function?? AICR, it is impossible. In addition, it's not suggested using arrays at all: the array based logic is foreign from SQL solutions. In fact PostgreSQL doesn't have a good array support. My friend who built our logic data model, said that the pg_group table is a bad idea in PostgreSQL, he would have created a new table to store relations between groups and users. I don't think it can be modified after such a long time. Regards, Zoltan
[SQL] to_char() causes backend to close connection
Hi, this query gives different strange results: select to_char(now()::abstime,'YYMMDDHH24MI'); I get e.g. a "backend closed the channel unexpectedly..." error with successful or failed resetting attempt (indeterministic) or ERROR: to_char/to_number(): not unique decimal poit or NOTICE: PortalHeapMemoryFree: 0x0x8201af0 not in alloc set My machine works with PostgreSQL 7.0.2 + Red Hat 6.1. Is this fixed in 7.0.3? TIA, Zoltan
Re: [SQL] to_char() causes backend to close connection
On Thu, 14 Dec 2000, Kovacs Zoltan Sandor wrote: > Hi, this query gives different strange results: > > select to_char(now()::abstime,'YYMMDDHH24MI'); the result: ERROR: to_char/to_number(): not unique decimal poit is right, because we have two to_char(): to_char(int, text) to_char(timestamp, text) and for 'now()::abstime' function manager select "number" version instead timestamp. And in "number" version is D as decimal point. > I get e.g. a "backend closed the channel unexpectedly..." error with > successful or failed resetting attempt (indeterministic) Yes this is a bug to_char() ... I fix it today. > My machine works with PostgreSQL 7.0.2 + Red Hat 6.1. > Is this fixed in 7.0.3? A problem (IMHO it's not problem) with 'now()::abstime' is in 7.1 too. But why you not use directly now()? Thanks! Karel
Re: [SQL] How to represent a tree-structure in a relational database
There actually is a model of tree structures in SQL databases which is different from those mentioned earlier in that it represents the tree as nested sets (ie. nodes are subsets of parent sets (parent nodes)). There is a huge advantage in this model as it eliminates the need for recursion. For example, if you want to get a specific node's parents, grandparents and all other parents up the tree, you can do this in _one single_ SELECT query. If you just stored the the id of the parent of each node, you would need (n-1) queries if the node is at the n-th level. However, inserting or deleting nodes is more complex this way since you have to keep the data structure "balanced". But in most cases you won't insert new nodes all the time so you won't suffer from this overhead. And on the other hand, the performance gain on queries might be enormous. You can find the article dealing with this at http://www.utdt.edu/~mig/sql-trees Actually what I did was to implement _both_ models at the same time. I took the nested set structure, and besides stored the parent id of all nodes as well. The benefit of this is that by accepting a little more overhead during inserts, I was able to use the advantages of both models. If somebody is interested in it, I might share the code (a few tables and some plpgsql functions). But the article is surely worth a read. Zsolt ps: I found the link a few months ago in the pgsql-sql archive :-) On Wed, 13 Dec 2000, Josh Berkus wrote: > Stuart, > > > I don't think I'd be comfortable with having the node_level column in the > > table structure. First, because you can derive that value using a function, > > it's duplicate data. Second, if you decide to take an entire segment of your > > hierarchy and move it under another node (by changing the value of > > node_linkup/ParentCategoryID), you'll need to recalculate all of those > > node_level values. And all the node_level values underneath it. > > I can see that. I suppose it depends on the data you're storing. The > project I was working on tracked grocery inventory for a delivery > service, and thus each item had a fixed "level" in the heirarcy (Food > Class, Food Type, Manufacturer, and Item) and thus while items might get > reassigned *across* the heirarcy, they did not get re-assigned *up and > down* the heirarcy. > > Also, I can't think of a way to represent the tree in pure SQL without > having the level identifiers (and a fixed number of levels). > > > We've done a similar thing for Java. It was ridiculously easy to create a > > TreeModel wrapped around this data. Almost too easy; it made me feel dirty. > > Great. Maybe I'll buy it from you if I ever need to use Java :-) > > -Josh > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 >for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco >
Re: [SQL] Strange slow behavior in backend
Tom Lane wrote:
> Kyle <[EMAIL PROTECTED]> writes:
> > This worked great until I put a real big file in (about 5M). Then, when
> > I tried to fetch the file, it seemed really slow (about 60 seconds). I
> > tried reassembling the file in the frontend instead and my time dropped
> > to about 6 seconds using this TCL fragment (mpg::qlist is an interface
> > to pg_exec that returns a list of tuples):
> > The only difference I can identify is whether the re-assembly TCL code
> > is running as a procedural language (backend) or in the frontend.
> > Anyone have any idea why the difference is so dramatic?
>
> I happened to have handy a 7.1 backend compiled for profiling, so I
> looked into this a little. I confirm that this seems unreasonably slow.
> As near as I can tell, 98% of the backend runtime is being spent in
> strlen() and strcpy() invoked from Tcl_SetResult invoked from Tcl_Eval
> invoked from the per-result-tuple loop in pltcl_SPI_exec. Apparently,
> all this is happening because Tcl_Eval thinks it needs to make the
> result of the append command available for its caller. I changed the
> inner loop to
>
> spi_exec -array d "select data from pg_largeobject where
> loid = $1 order by pageno" {
> append odata $d(data);
> set z z
> }
>
> and voila, the runtime dropped to something reasonable.
>
> So, yes, it would seem that some care in the inner loop of
> pltcl_SPI_exec would help a lot. It'd be worth if'defing the Tcl_Eval
> call there to use a new-style call when using Tcl 8. (This could also
> avoid repetitive parsing of the loop body.) Might want to think about
> the same for the Tcl function as a whole, too.
>
> I was also distressed to notice that pltcl_set_tuple_values does a LOT
> of repetitive work --- it should be fixed so that the syscache and
> function lookups are done only once, not once per tuple.
Hmmm - seems worse than I thought.
Again, let's wait with all that until we can overhaul it
including the tupleset return mechanism and simply loose
pre-8.0 compatibility.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
Re: [SQL] Strange slow behavior in backend
Kyle wrote:
> I'm using 7.0.1 with a TCL frontend.
>
> I have a schema that splits large files into tuple-sized bites and
> stores them in a table. This was done before TOAST in order to store
> large files.
>
> I have a backend TCL function that re-assembles the file like this:
>
> -- Fetch the specified document data, reassembling the bits back
> together
> -- in the right order.
> -- Calling sequence: cont_doc_fetch(crt_by,crt_date,ctype)
> create function cont_doc_fetch(int4,timestamp,varchar) returns text as '
>
> set odata {}
> spi_exec -array d "select data from cont_doc_data where crt_by =
> \'$1\' and crt_date = \'$2\' and ctype = \'[quote $3]\' order by seq" {
> append odata $d(data)
> }
> return $odata
> ' LANGUAGE 'pltcl';
>
> This worked great until I put a real big file in (about 5M). Then, when
> I tried to fetch the file, it seemed really slow (about 60 seconds). I
> tried reassembling the file in the frontend instead and my time dropped
> to about 6 seconds using this TCL fragment (mpg::qlist is an interface
> to pg_exec that returns a list of tuples):
>
> set data {}
> set tuple_list [mpg::qlist "select data from $ca(prefix)_doc_data
> where crt_by = $crt_by and crt_date = '$crt_date' and ctype = '$ctype'
> order by seq"]
> foreach rec $tuple_list {
> append data [lindex $rec 0]
> }
>
> The only difference I can identify is whether the re-assembly TCL code
> is running as a procedural language (backend) or in the frontend.
> Anyone have any idea why the difference is so dramatic?
>
> Jan:
> Is this the difference between old TCL and new TCL (with multi-port
> objects)? Or is there something else about the way the backend handles
> large chunks of data that would mark the difference?
That's it. It shouldn't have to do with the amount of data
invoked, but with the number of tuples returned by spi_exec
and spi_execp commands.
Due to backwards compatibility, all commands in the PL/Tcl
handler still use the old string interface. Thus, the
procedure text for each tuple (in your case "append odata
$d(data)") is evaluated from it's string representation again
and again, needs to be interpreted and precompiled for each
single tuple by the Tcl interpreter. I think using Tcl_Obj's
here would cause a substantial improvement.
I plan to do a major overhaul of PL/Tcl after we have an
interface for functions returning tuple sets. This will
include loosing the backward compatibility to pre-8.0
releases of Tcl because of using the Tcl_Obj interface only.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
Re: [SQL] How to represent a tree-structure in a relational database
somebody already showed table structure, but i'll ad some more code to this:
table:
CREATE TABLE groups (
id INT4 NOT NULL DEFAULT NEXTVAL('groups_seq'),
parent_idINT4 NOT NULL DEFAULT 0,
name TEXT NOT NULL DEFAULT '',
active BOOL NOT NULL DEFAULT 't'::bool,
PRIMARY KEY (id)
);
INSERT INTO groups (id) VALUES (0);
ALTER TABLE groups ADD FOREIGN KEY (parent_id ) REFERENCES groups (id);
CREATE UNIQUE INDEX groups_pn_u ON groups (parent_id, name, active);
at this point it seems to be pretty easy and obvious.
in my case i got to the point that i needed some more info about the branch of
tree. so i wrote:
REATE function getgrouppath(int4, text) returns text as '
DECLARE
sep ALIAS FOR $2;
aid int4;
wynik TEXT;
temp RECORD;
b BOOL;
BEGIN
b:=''t'';
wynik:=;
aid:=$1;
while b loop
SELECT name, parent_id INTO temp FROM groups WHERE id=aid;
IF NOT FOUND THEN
return wynik;
END IF;
if wynik = THEN
wynik:=temp.name;
else
wynik:=temp.name||sep||wynik;
END if;
IF temp.parent_id = 0 THEN
b:=''f'';
ELSE
aid:=temp.parent_id;
END if;
end loop;
return wynik;
END;
' language 'plpgsql';
(sorry for polish variable names)
this function does one nice trick
when having structure like:
=> select id, parent_id, name, active from groups;
id | parent_id | name | active
+---+--+
0 | 0 | | t
1 | 0 | RTV | t
2 | 0 | AGD | t
3 | 0 | MP3 | t
4 | 1 | Audio| t
5 | 2 | Lodówki | t
6 | 2 | Kuchenki Mikrofalowe | t
7 | 4 | Sony | t
8 | 4 | Panasonic| t
(9 rows)
i can:
=> select id, parent_id, name, active, getgrouppath(id, '/') from
groups;
id | parent_id | name | active | getgrouppath
+---+--++--
0 | 0 | | t |
1 | 0 | RTV | t | RTV
2 | 0 | AGD | t | AGD
3 | 0 | MP3 | t | MP3
4 | 1 | Audio| t | RTV/Audio
5 | 2 | Lodówki | t | AGD/Lodówki
6 | 2 | Kuchenki Mikrofalowe | t | AGD/Kuchenki Mikrofalowe
7 | 4 | Sony | t | RTV/Audio/Sony
8 | 4 | Panasonic| t | RTV/Audio/Panasonic
since for some reasons (indenting) i needed the level of branch i wrote:
CREATE FUNCTION grouplevel(int4) returns int4 AS '
DECLARE
baseid ALIAS FOR $1;
currid INT4;
reply INT4;
BEGIN
reply:=1;
if baseid = 0 then return 0; END if;
SELECT parent_id INTO currid FROM groups where id=baseid;
while currid>0 loop
reply:=reply+1;
SELECT parent_id INTO currid FROM groups where id=currid;
END loop;
return reply;
END;
' language 'plpgsql';
which also seems pretty obvious.
to be complete i wrote two triggers which made me happy:
CREATE FUNCTION trg_recurs_act_g() RETURNS OPAQUE AS '
BEGIN
IF NEW.active=''f''::bool and OLD.active=''t''::bool THEN
UPDATE articles SET active=''f''::bool WHERE group_id=NEW.id;
UPDATE groups SET active=''f''::bool WHERE parent_id=NEW.id and id<>0;
ELSE
IF NEW.active=''t''::bool and OLD.active=''f''::bool AND NEW.id<>0 THEN
UPDATE groups SET active=''t''::bool WHERE id=NEW.parent_id;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION trg_recurs_act_a() RETURNS OPAQUE AS '
BEGIN
IF NEW.active=''t''::bool and OLD.active=''f''::bool THEN
UPDATE groups SET active=''t''::bool WHERE id=NEW.group_id;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER groups_update_trg BEFORE UPDATE ON groups FOR EACH ROW EXECUTE
PROCEDURE trg_recurs_act_g();
CREATE TRIGGER articles_update_trg BEFORE UPDATE ON articles FOR EACH ROW EXECUTE
PROCEDURE trg_recurs_act_a();
as you can see those triggers use article table which structure is not
important at this moment (let's assume it has id, group_id, name and active).
i hope this code will help you a bit.
depesz
--
hubert depesz lubaczewski
najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [SQL] to_char() causes backend to close connection
> A problem (IMHO it's not problem) with 'now()::abstime' is in 7.1 too. > But why you not use directly now()? My aim is to get this format for the time '5 minutes ago'. So I need something like select to_char(now()-'5 minutes','YYMMDDHH24MI') but this doesn't work due to the missing type conversion. Any idea? TIA, Zoltan
RE: [SQL] to_char() causes backend to close connection
Title: RE: [SQL] to_char() causes backend to close connection select to_char(now()-'5 minutes'::interval,'YYMMDDHH24MI') seems to work Ben > -Original Message- > From: Kovacs Zoltan Sandor [mailto:[EMAIL PROTECTED]] > Sent: 14 December 2000 15:49 > To: Karel Zak > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] to_char() causes backend to close connection > > > > A problem (IMHO it's not problem) with 'now()::abstime' is > in 7.1 too. > > But why you not use directly now()? > My aim is to get this format for the time '5 minutes ago'. So I need > something like > > select to_char(now()-'5 minutes','YYMMDDHH24MI') > > but this doesn't work due to the missing type conversion. > > Any idea? TIA, Zoltan >
[SQL] pg_control error!
Hi all!
I'm using postgresql7.0.2
I did
ipcclean
and then
pg_ctl stop
then I tried to start up again the DB server as usual
nohup postmaster -i > pgserver.log 2>&1 &
I got this error:
FATAL 2: Read("/usr/local/pgsql/data/pg_control") failed:2
FATAL 2: Read("/usr/local/pgsql/data/pg_control") failed:2
Startup failed - abort.
Thank you for the help,
bye.
Rocael
Get free email and a permanent address at http://www.netaddress.com/?N=1
[SQL] Howto compile fti.c
I can´t compile fulltext index, i need step by step explain about this process and Where are fti.so file? please help me to implement this feature. I have Postgresql 7.0 in linux red-hat Thanks
[SQL] Compiling "C" Functions
Hi, ALL This is my first time in this list... Sorry by any inconvenience I need a TRIGGER thats runs whith POSTGRES permissions. I´ll give an ODBC connection for programmers that don´t need to know the "rules" of my Data Base. So, I´ll creata an "fake" table, and with a BEFORE INSERT TRIGGER I´ll make all consistences, and update the REAL tables. The ODBC users have only INSERT permission on the fake table, and NO RIGHTS on the real table. I´ll make VIEWS whith SELECT, UPDATE and DELETE permissions for the ODBC users. By the same way, I´ll make TRIGGERS that manager the VIEWS. --- ALMOST ALL THIS WORK FINE when the function of the trigger is in PLPGSQL, but in this language, the Trigger run with the same ODBD user´s rights I read that only a "C" function runs with POSTGRES permissions, but all the samples I get don´t work... I can´t even compile them... Please, anyone could help an "poor brazilian guy" to make this work :-) ? I appreciate any "C" Function complete samples, including de command line for the compiler. I using a RedHat Linux 6.2 and Postgresql 7.0.2-2 Best Regards, Tulio Oliveira. -- == AKACIA TECNOLOGIA Desenvolvimento de sistemas para Internet www.akacia.com.br
[SQL] pg_control error!
Hi all!
I'm using postgresql7.0.2
I did
ipcclean
and then
pg_ctl stop
then I tried to start up again the DB server as usual
nohup postmaster -i > pgserver.log 2>&1 &
I got this error:
FATAL 2: Read("/usr/local/pgsql/data/pg_control") failed:2
FATAL 2: Read("/usr/local/pgsql/data/pg_control") failed:2
Startup failed - abort.
Thank you for the help,
bye.
Rocael
Get free email and a permanent address at http://www.netaddress.com/?N=1
Re: [SQL] How to represent a tree-structure in a relationaldatabase
Hi, miguel sofer wrote: > > I once started writing a small paper on this subject; it is still in a > rather preliminary state. > > You can download the draft (and some ill documented code, 53kB) from > http://www.utdt.edu/~mig/sql-trees ah, this looks very, very nice! on page 5ff you describe the Postgres implementation, but the URL (page 5 bottom) is't complete -- can i find the files somewhere? Included is a "tree_general.sql", but this seems not to be complete and not the same version as the ps-file (First draft, may 6, 2000): in the draft there is written about an base 160 encoding, tree_general.sql uses base 159 encoding ;) Ciao Alvar -- Alvar C.H. Freude | [EMAIL PROTECTED] Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
Re: [SQL] How to represent a tree-structure in a relationaldatabase
Hi, > > > I once started writing a small paper on this subject; it is still in a > > rather preliminary state. > > > > You can download the draft (and some ill documented code, 53kB) from > > http://www.utdt.edu/~mig/sql-trees > i guess, with base 160 encoding there might be a problem: if postgres is compiled with --enable-locale (e.g. for german umlauts), the ordering isn't according to the ASCII number of the character, so for this purpose it's needed to build the encoding table according to the locate settings. Or simply sort it according the locale settings. What's against using all characters >= 32, excluding special characters with special meaninbg in LIKE and regexps? With base 208 encoding it's possible to have 43264 elements on each level. Ciao Alvar -- Alvar C.H. Freude | [EMAIL PROTECTED] Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
Re: Secure Messaging Non-Delivery Report: Re: [SQL] How to represent a tree-structure in a relationaldatabase
Hi Security-Master, [EMAIL PROTECTED] wrote: > > When [EMAIL PROTECTED] sent e-mail to [EMAIL PROTECTED] >(1/255/1) > The message was blocked by TFS Secure Messaging > When [EMAIL PROTECTED] sent e-mail to [EMAIL PROTECTED] >(1/255/1) > The message was blocked by TFS Secure Messaging please check your security settings. I sent mail to Postgres Mailing list, it seems that [EMAIL PROTECTED] is subscibes there but has no right to receive emails from everyone. BTW: it seems to me that it makes no sense to use at the one hand internet and at the other to block all. So, if you don't want internet, make your internal Intranet ... Ciao Alvar -- Alvar C.H. Freude | [EMAIL PROTECTED] Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
Re: Secure Messaging Non-Delivery Report: Re: [SQL] How to represent a tree-structure in a relationaldatabase
As of a few moments ago, Dana Reed is an ex-subscriber to pgsql-general. Evidently he/she is about to be an ex-subscriber to pgsql-sql as well. Most mailing list admins take a very dim view of mail software that sends bounces to the original author of a mailing-list message, rather than to the mailing list's errors address (the "envelope sender" in SMTP parlance). I don't run the pgsql lists myself, but on lists I do run, bouncing to authors who can't do anything about it is grounds for instant removal from the list. I believe Marc feels the same. If you get bounces from third-party recipients of pgsql list messages, let Marc know ([EMAIL PROTECTED]). There's no point in bothering the rest of the list with it, though --- Marc is the only one in a position to do anything about it. regards, tom lane
Re: Secure Messaging Non-Delivery Report: Re: [SQL] How to representa tree-structure in a relationaldatabase
oh, wait, you didn't want me to remove dana from all lists? :) if
offensive on one, I figure its offensive on all ... saves time, no?
On Thu, 14 Dec 2000, Tom Lane wrote:
> As of a few moments ago, Dana Reed is an ex-subscriber to pgsql-general.
> Evidently he/she is about to be an ex-subscriber to pgsql-sql as well.
>
> Most mailing list admins take a very dim view of mail software that
> sends bounces to the original author of a mailing-list message, rather
> than to the mailing list's errors address (the "envelope sender" in SMTP
> parlance). I don't run the pgsql lists myself, but on lists I do run,
> bouncing to authors who can't do anything about it is grounds for
> instant removal from the list. I believe Marc feels the same. If you
> get bounces from third-party recipients of pgsql list messages, let Marc
> know ([EMAIL PROTECTED]). There's no point in bothering the rest of the
> list with it, though --- Marc is the only one in a position to do
> anything about it.
>
> regards, tom lane
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: Secure Messaging Non-Delivery Report: Re: [SQL] How to representa tree-structure in a relationaldatabase
Hi, The Hermit Hacker schrieb: > > oh, wait, you didn't want me to remove dana from all lists? :) if > offensive on one, I figure its offensive on all ... saves time, no? hmm, perhaps the admin of him give OK to receive mails in future ... ;-) Ciao Alvar -- Alvar C.H. Freude | [EMAIL PROTECTED] Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
