Re: [GENERAL] a JOIN on same table, but 'slided over'

On 6/26/07, Tom Lane <[EMAIL PROTECTED]> wrote: "news.gmane.org" <[EMAIL PROTECTED]> writes: > Gurjeet Singh skrev: >> Also note that this query is much cheaper that the 'distinct on' query >> by more than two orders on magnitude ( 217.86 vs. 98040.67): > No it isn't. The estimate is much lower

Re: [GENERAL] growing disk usage problem: alternative solution?

On Jun 26, 2007, at 3:31 PM, Bill Moran wrote: VACUUM FULL and REINDEX are not required to maintain disk usage. Good old- fashoned VACUUM will do this as long as your FSM settings are high enough. I find this true for the data but not necessarily for indexes. The other week I reindex

Re: [GENERAL] AutoVacuum Behaviour Question

Bruce McAlister wrote: > I have just checked the pg_stat_all_tables in the pg_catalog schema and > I can see the index scans etc table values incrementing. The data in the > tables seems to be updating. Just an FYI, I've enabled manual vacuum > analyze runs on the blueface-service database up unti

Re: [GENERAL] AutoVacuum Behaviour Question

Alvaro Herrera wrote: > Bruce McAlister wrote: >> Hi All, >> >> I have enabled autovacuum in our PostgreSQL cluster of databases. What I >> have noticed is that the autovacuum process keeps selecting the same >> database to perform autovacuums on and does not select any of the others >> within the

Re: [GENERAL] a JOIN on same table, but 'slided over'

OK, check... test=> CREATE TABLE test (id INTEGER PRIMARY KEY); test=> INSERT INTO test SELECT random()*5 + n*10 FROM generate_series( 1,10 ) AS n; test=> SELECT * FROM test LIMIT 10; id - 11 23 31 41 52 63 70 85 94 103 test=> ANALYZE test; ANALYZE

Re: [GENERAL] Ordering in SELECT statement

On Jun 26, 2007, at 14:41 , [EMAIL PROTECTED] wrote: and what I need is the following ("old fashion", that is, the "SPACE" is another character whose ASCII value is before any other LATIN letter's!!) AB CD AB EF ABAB ABD E What you don't want : peufeu=> SELECT column1 FROM (VALU

Re: [GENERAL] escaped rolenames in pg_has_role

er.. i guess i was misinformed. (by Korry & Susan Douglas´ second edition, maybe this is relatively new?) However, could you please answer my question? On 6/26/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Willy-Bas Loos" <[EMAIL PROTECTED]> writes: >> A quote in the data might be real data or quot

Re: [GENERAL] LC_CTYPE and matching accented chars

Alvaro Herrera wrote: > Martin Langhoff wrote: >> # this is apparently the right way to >> # select base character based on the "equivalence class" >> # as defined in the LC_CTYPE >> =# select * from test where value ~ 'mart[=i=]n'; > > I think it would be much easier if yo

Re: [GENERAL] a JOIN on same table, but 'slided over'

I see. (Have actually tried it on a larger dataset - to see it for myself ... it is optimised :) Thenx again! -R On Tue, 2007-06-26 at 19:56 +0530, Gurjeet Singh wrote: > It _is_ the optimised version as you can see from the explain > plans posted in the other mail, the planner shows th

Re: [GENERAL] upgrade 8.1.4 -> latest, sort order subquery

Richard Huxton <[EMAIL PROTECTED]> writes: > jef peeraer wrote: >> registratie=# select * from module_info where type_module_id in >> (select * from get_parent_type_modules(1)); >> The order is completely ignored, although there is an order by in the view >> 'module_info' > You're applying a wher

Re: [GENERAL] escaped rolenames in pg_has_role

"Willy-Bas Loos" <[EMAIL PROTECTED]> writes: >> A quote in the data might be real data or quoting, and you have no way to >> tell the difference. > It's not so bad, because a parameter of type name cannot start with a quote. Sure it can. regression=# select '"Fooey"'::name; name -

Re: [GENERAL] upgrade 8.1.4 -> latest, sort order subquery

On Jun 26, 2007, at 14:52 , jef peeraer wrote: The order is completely ignored, although there is an order by in the view 'module_info' I don't know what has caused the change in your system, but IIRC, the spec does not require a view to return rows in any particular order— I'm not even

Re: [GENERAL] upgrade 8.1.4 -> latest, sort order subquery

jef peeraer wrote: i decide this weekend to upgrade to the latest stable version from an 8.1.4 . Upgrade went smootly, as usual, but today, i've got some phonecalls of something weird. The query is as follows : registratie=# select * from module_info where type_module_id = 1; i combine this

Re: [GENERAL] Ordering in SELECT statement

On Jun 26, 2007, at 14:41 , [EMAIL PROTECTED] wrote: and what I need is the following ("old fashion", that is, the "SPACE" is another character whose ASCII value is before any other LATIN letter's!!) AB CD AB EF ABAB ABD E Sorting is defined by the locale settings of your computer. I get

Re: [GENERAL] Ordering in SELECT statement

[EMAIL PROTECTED] wrote: Hello, I use the following command "SELECT * FROM employees ORDER BY name" (a very simple sql statement) the main thing here is that I get a list ordered without taking into accound the spaces. For example, I get the following listing: ABAB AB CD ABD E

[GENERAL] upgrade 8.1.4 -> latest, sort order subquery

i decide this weekend to upgrade to the latest stable version from an 8.1.4 . Upgrade went smootly, as usual, but today, i've got some phonecalls of something weird. The query is as follows : registratie=# select * from module_info where type_module_id = 1; naam | korte_beschrijving | kolom_

Re: [GENERAL] Throwing exceptions

On Jun 25, 2007, at 19:01 , Michael Glaesemann wrote: The message is just a string. Assign the message to a variable and use the variable in place of the message. For example, in PL/pgSQL: k_error_message := 'Boom! %'; RAISE EXCEPTION k_error_message, v_foo.id; I was wrong. The message is

Re: [GENERAL] growing disk usage problem: alternative solution?

In response to [EMAIL PROTECTED]: > Hello List > > I have been a user since 7.x version. My current server version is 8.1.2. > > As many of you know the disk usage keeps growing for postgresql unless one > regularly reindex and/or fully vacuum. The problem with either method is > that they l

[GENERAL] Ordering in SELECT statement

Hello, I use the following command "SELECT * FROM employees ORDER BY name" (a very simple sql statement) the main thing here is that I get a list ordered without taking into accound the spaces. For example, I get the following listing: ABAB AB  CD ABD  E AB  EF and what

Re: [GENERAL] escaped rolenames in pg_has_role

A quote in the data might be real data or quoting, and you have no way to tell the difference. It´s not so bad, because a parameter of type name cannot start with a quote. But you´re right, i have been having a hard time with the use of quoting. The rule i used until now was: "every string that

Re: [GENERAL] growing disk usage problem: alternative solution?

[EMAIL PROTECTED] wrote: Hello List I have been a user since 7.x version. My current server version is 8.1.2. As many of you know the disk usage keeps growing for postgresql unless one regularly reindex and/or fully vacuum. The problem with either method is that they lock the tables and it

[GENERAL] growing disk usage problem: alternative solution?

Hello List I have been a user since 7.x version. My current server version is 8.1.2. As many of you know the disk usage keeps growing for postgresql unless one regularly reindex and/or fully vacuum. The problem with either method is that they lock the tables and it is not practical for databa

Re: [GENERAL] escaped rolenames in pg_has_role

"Willy-Bas Loos" <[EMAIL PROTECTED]> writes: > The problem is that pg_has_role does not recognize the usernames when they > are escaped by quote_literal or quote_ident. Don't use quote_ident here. In fact, I'd say that you appear to have completely misunderstood the appropriate rules for quoting

Re: [GENERAL] A problem in inheritance

to impact that update you'd have to have a rule on account_login. No rule on a child table will matter. Well i had the same perception In order to double check this i created an On insert do nothing rule on the child table and did an insertion to the master table i.e (account_login) but this

Re: [GENERAL] problem importing data with psql

chandresh rana wrote: Hi Richard, I have eliminated certain tables while exporting as they the size of the data in the tables are huge.Am having the schema of all the tables. Now want to import certain set of records/rows from the eliminated tables. Is it possible to do that ?? If yes, then ca

Re: [GENERAL] a JOIN on same table, but 'slided over'

"news.gmane.org" <[EMAIL PROTECTED]> writes: > Gurjeet Singh skrev: >> Also note that this query is much cheaper that the 'distinct on' query >> by more than two orders on magnitude ( 217.86 vs. 98040.67): > No it isn't. The estimate is much lower, but the actual times are very > close: > [explai

[GENERAL] View Triggers

It seems that insertion triggers on views will/may be added in version 8.3. http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php However, Tom mentioned that adding update and deletion triggers may be a bad idea: http://archives.postgresql.org/pgsql-general/2007-02/msg01466.php ... Ho

Re: [GENERAL] a JOIN on same table, but 'slided over'

Gurjeet Singh skrev: > I missed the ORDER BY clause... Here it goes: > > selectt1.id as id, t2.id as "id+1", > t1.thread as thread, t2.thread as "thread+1", > t1.info as info, t2.info as > "info+1" > from test as t

Re: [GENERAL] Rule vs Trigger

Tom Lane wrote: "Albe Laurenz" <[EMAIL PROTECTED]> writes: Richard Broersma Jr wrote: A rule would only execute one additional statement. So if you can do it with a rule conveniently, the rule will probably be faster. I find this unlikely. The overhead involved in setting up a rule is prob

Re: [GENERAL] Duplicate records returned

In article <[EMAIL PROTECTED]>, Richard Huxton <[EMAIL PROTECTED]> writes: >> $the_sql = " SELECT projectname, username, sum(hours)"; >> $the_sql .= " FROM timerecs"; >> $the_sql .= " WHERE projectname = projects.projectname "; >> $the_sql .= " AND projectname = restrictions.projectname"; >> $the

Re: [GENERAL] Rule vs Trigger

>> A trigger FOR EACH STATEMENT will execute the trigger function >> for each row affacted by the statement. > > Huh? That would be true for a FOR EACH ROW trigger, but a STATEMENT > trigger fires once per statement. Argh. I intended to write FOR EACH ROW. Thanks for the correction. >> A rule w

[GENERAL] pg_catalog.pg_get_serial_sequence() returns NULL

Hi! I use PG 8.1.5 I execute in psql next comands: create table t_table (gid serial, name varchar); select pg_catalog.pg_get_serial_sequence('t_table', 'gid'); pg_get_serial_sequence public.t_table_gid_seq create table t_table_1() inherits (t_table); \d t_table_1

Re: [GENERAL] how to implement unusual constraint

On Sun, Jun 24, 2007 at 06:45:00PM -, danmcb wrote: > into two tables, one for the originals, one for the translations (the > objects are actually phrases in many languages). But I'd rather avoid > that because in all other ways, the objects have the same properties, > reference the same object

[GENERAL] escaped rolenames in pg_has_role

Dear General, I have stolen some code from information_schema.applicable_roles, so that i can query the roles for a user, without having to become that user (a superuser executes this). The problem is that pg_has_role does not recognize the usernames when they are escaped by quote_literal or quot

Re: [GENERAL] LC_CTYPE and matching accented chars

Martin Langhoff wrote: > Trying to understand how I can get Pg 8.2 to match 'martín' when I > search for 'martin', and going through the documentation, specially > > http://www.postgresql.org/docs/8.2/static/locale.html > http://www.postgresql.org/docs/8.1/static/functions-matching.html > > H

Re: [GENERAL] Rule vs Trigger

"Albe Laurenz" <[EMAIL PROTECTED]> writes: > Richard Broersma Jr wrote: >> My understanding is that Triggers offer better performance >> than rules do. > A trigger FOR EACH STATEMENT will execute the trigger function > for each row affacted by the statement. Huh? That would be true for a FOR EAC

Re: [GENERAL] a JOIN on same table, but 'slided over'

I missed the ORDER BY clause... Here it goes: selectt1.id as id, t2.id as "id+1", t1.thread as thread, t2.thread as "thread+1", t1.info as info, t2.info as "info+1" from test as t1, test as t2 where t2.id = ( select min(id) from test as t3 where t3.id > t1.id ) order by t1.id as

Re: [GENERAL] a JOIN on same table, but 'slided over'

OK. Have tried this one looks like close to 6 times slower then the 'non-standard' phrase with 'distinct on'. On the small dataset that I've included in my original post (ten rows of data within TEST), I've run both queries through EXPLAIN ANALYSE, with the following result summary (for clear

Re: [GENERAL] dynamic table/col names in plpgsql

On Sat, Jun 23, 2007 at 09:43:01PM -0600, gary jefferson wrote: > Is there a way to use a variable as the name of a table or column in > plpgsql? No, plpgsql is statically typed, it can't handle the possiblity of the types of variables changing. Use a more dynamic language(perl/tcl/python/etc...)

Re: [GENERAL] a JOIN on same table, but 'slided over'

Hi Rafal, Just a note that this is not standard SQL... 'distinct on' is an extension to SQL provided by postgres. Following query utilizes the standard SQL to get the same results: selectt1.id as id, t2.id as "id+1", t1.thread as thread, t2.thread as "thread+1", t1.info as

Re: [GENERAL] a JOIN on same table, but 'slided over'

Marvelous! Thenx! -R On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote: > On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote: > Is there an SQL construct to get it? > > select > distinct on (t1.id) > t1.*, t2.* > from > test t1 > join test t2 on t2.id >

Re: [GENERAL] Rule vs Trigger

Richard Broersma Jr wrote: > --- Alfred Zhao <[EMAIL PROTECTED]> wrote: >> Suppose I want to update a statistics table S after an >> insertion into an activity table A. In order to update S >> automatically, I can either create a rule on A or create >> an after trigger on A. What's the performance

Re: [GENERAL] postgresql varchar[] data type equivalent in Oracle

Murali Doss wrote: > > I like to know about postgresql varchar[ ] data type > equivalent in Oracle. The best I can think of is a VARRAY, though you cannot access it by index in SQL (you need a stored procedure or client API for that). Quite clumsy. Example: SQL> CREATE TYPE VARCHAR_A AS VARRAY

Re: [GENERAL] problem importing data with psql

cha wrote: But am not able to import the tables with batch file, though am able to run the same command from the prompt successfully. Why? What errors are you getting? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't for

Re: [GENERAL] Duplicate records returned

[EMAIL PROTECTED] wrote: I'm getting duplicate rows returned. I don't know know and can't find out how to construct the SQL to return what I want. I have an old version of postgres which I cannot upgrade. I'm not even sure how to get the version. Does this help? $ grep -i version /pi/bin

Re: [GENERAL] a JOIN on same table, but 'slided over'

Now, I'd like to make a JOIN-ed query of that table with itself, so that I'd get rows paiwise: every row containing data from *two* rows of the original TEST table so, that those data come from rows of consequtive ID's - not neceserly (depending on the TEST table contents) continuesly consequtiv

Re: [GENERAL] data partitions across different nodes

On 6/26/07, 金星星 <[EMAIL PROTECTED]> wrote: Are there any solutions based on PostgreSQL that can support distributing partitions (horizontal fragmentations) across different nodes. It doesn't need to support distributed transaction, since data inconsistent is not a critical problem in my situatio

Re: [GENERAL] a JOIN on same table, but 'slided over'

On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote: Is there an SQL construct to get it? select distinct on (t1.id) t1.*, t2.* from test t1 join test t2 on t2.id > t1.id order by t1.id asc, t2.id asc should do the trick. depesz -- http://www.depesz.com/ - nowy, lepszy depesz

[GENERAL] a JOIN on same table, but 'slided over'

Hi, I understand, that this is 'general SQL' question rather then 'general postgres'. But may be someone here could help me with it anyways. I have a *single* table: CREATE TABLE test (id int not null unique, thread int not null, info text); The ID, although unique, is not continues. A sample q