Re: [HACKERS] 8.2 features status
On Thu, Aug 10, 2006 at 09:02:36PM -0700, Joshua D. Drake wrote: > I think it is a combination of the two. A wiki could be used to discuss > ideas for todos, it could be used to describe TODOs in actual detail, it > could used (in conjunction with Trac) to be able to document dependecies > for todos... etc. A wiki for *discussion*? I thought email was for that. A wiki is nice to work toghether on a document (in some circumstances). -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] POWER vs. POW ???
On Thu, Nov 24, 2005 at 08:00:21PM +0800, Christopher Kings-Lynne wrote: > How come these give slightly different results? > > test=# SELECT POW(2,-2); > pow > -- > 0.25 > (1 row) > > test=# SELECT POWER(2,-2); > power > --- > 0.25 > (1 row) > > > (Note width of result field.) The result field is the length of min(the string, printable result) + 2: # SELECT POW(2,-2); pow -- 0.25 (1 row) # SELECT POW(2,-2) as power; power --- 0.25 (1 row) # SELECT POW(2,-2) as p; p -- 0.25 (1 row) -- ______ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] US Patents vs Non-US software ...
On Tue, Jan 18, 2005 at 11:38:45AM -0800, J. Andrew Rogers wrote: > On Tue, 18 Jan 2005 09:22:58 +0200 > >Many countries do not grant software patents so it is not > >likely > >that IBM applied through PCT since a refusal in one > >country may > >cause to patent to be refused in all countries. > > > Contrary to popular misconception, virtually all countries > grant software patents. The problem is that people have Thanks to the new European Union member Poland, the Dutch plan to put the software patents on the agenda 3 days before Christmas was revoked. So no software patents in Europe for now. (and the opposition against it seems to grow!) -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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: Postgres development model (was Re: [HACKERS] CVS comment)
On Mon, Aug 09, 2004 at 09:30:09AM +0200, Peter Eisentraut wrote: > Tom Lane wrote: > > I haven't seen any particular reason why we should adopt another SCM. > > Perhaps BitKeeper or SubVersion would be better for our purposes than > > CVS, but are they enough better to justify the switchover costs? > > BitKeeper ist not open source, so it's out of the question for most > people. Why? I understood that using BitKeeper for free for Open Source projects is allowed. (but IANAL). It is available (on many platforms). It works great. Once you use changesets you'll never want to go back to cvs. Producing an Open Source product does not mean that all tools are Open Source. Windows isn't and Postgresql is going to support windows. -- __________ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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: [HACKERS] cache control?
On Fri, Jan 16, 2004 at 12:00:08PM -0500, Michael Brusser wrote: > Is there a way to force database to load > a frequently-accessed table into cache and keep it there? If it is frequently accessed, I guess it would be in the cachke permanently -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-advocacy] Not 7.5, but 8.0 ?
On Tue, Nov 18, 2003 at 12:18:51PM -0500, Andrew Sullivan wrote: > On Tue, Nov 18, 2003 at 08:39:29AM -0800, ow wrote: > > > > Have *never* seen ppl running Oracle or Sybase on Windows. > > I _have_ certainly seen plenty of people running Oracle on Windows. > They weren't necessarily happy, of course, but people do it all the > time. > > As for Sybase, you don't see that because Sybase on Windows was, for > a long time, SQL Server. Not exaclty. Sybase 4.21 = MS SQL server 4.21. But then they ended their relationship (much like MS and IBM did over OS/2). This was somewhere around the mid 90's. Since then Sybase has renamed their enterprise product to Adaptive Server Enterprise, and versions 10, 11, 11.5 and beyond have always been available on windows. A few years after they split up with Microsoft, they bought the product SQL Anywhere (forgot the firm they bought it from). It took them a few years to make this product 100% SQL compatible with ASE. This product was ported to some Unix platforms around that time too. -- __________ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] plpgsql strangeness with select into
I'm debugging a trigger in plpgsql and for some reason or the "select into " does not seem to work. Here is an unaltered snippet of my trigger code: raise notice ''this id : %'',NEW.id; select into i_hierarchy_id hierarchy_id from link_def LD, link L, object_link OL where OL.id = NEW.id and L.id = OL.link_id and LD.id = L.link_def_id; raise notice ''i_hierarchy_id: %'',i_hierarchy_id; in the log this results in: NOTICE: this id : 5265 NOTICE: i_hierarchy_id: but when I perform the query on the command line I do get a result: select hierarchy_id from link_def LD, link L, object_link OL where OL.id = 5264 and L.id = OL.link_id and LD.id = L.link_def_id; hierarchy_id -- 1 (1 row) i_hierarchy_id is declared as integer and is not used before this code nor as a column name anywhere. Does anyone have a clue what is going wrong? I use Postgresql 7.3.3 on FreeBSD 4.5. -- __________ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] how to make a trigger deferrable
Hi, I have a trigger in my database that checks to see if there is another record in the table, and when there is if the type is correct. (if the first one is of type "parent", the other has to be of type "child"). When updating these records in a transaction, the trigger only works when I make it deferrable. I hacked this by updating pg_trigger and setting the column tgdeferrable to true. IS there a way to do this in SQL? I can only find documentation on setting a constraint to deferrable, but not on a trigger. TIA, Reinoud -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Why an array in pg_group?
Hi, Is there any reason why the grolist field in the table pg_group is implemented as an array and not as a separate table? According to the documentation: Arrays are not sets; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. I have trouble implementing a way to easily check whether a user is part of a group. (I use Apache::AuthDBI to implement authentication and wanted to make a view with columns username, userid , groupname. And installing the contrib/array give's me a postgresql that is different from all the others :-( -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Why an array in pg_group?
Hi, Is there any reason why the grolist field in the table pg_group is implemented as an array and not as a separate table? According to the documentation: Arrays are not sets; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. I have trouble implementing a way to easily check whether a user is part of a group. (I use Apache::AuthDBI to implement authentication and wanted to make a view with columns username, userid , groupname. And installing the contrib/array give's me a postgresql that is different from all the others :-( -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(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
[HACKERS] status of IPv6 Support for INET/CIDR types
Hi, We are implementing a database for maintaining our IP addresses. Looking in the current documentation, it seems that INET/CIDR types only support IPv4 addresses until now, although http://archives.postgresql.org/pgsql-patches/2001-09/msg00236.php seems to suggest a patch for IPv6 has been ready for some time now. What is the status of IPv6 types at this moment? -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] What executes faster?
> [HACKERS] What executes faster? > Now that I've found the solution for my duplicate key problem, > I'm wondering what executes faster when I have to check for > duplicates. > 1. try to update >if no row affected -> do the insert >else done > 2. do a select >if row not found -> do the insert >else do the update > Another idea I'm thinking about: > I'm doing the check for duplicate key by myself now. > Aren't insert commands running faster, if I replace > an unique index by a not-unique index. I have solved an almost similar problem. I have a large table (about 8 milion rows) called radius and a table with updates and newlines called radiusupdate. The first thing I tried was 2 queries: update radius from radiusupdate where radius.pk = radiusupdate.pk insert into radius select * from radiusupdate RU where RU.pk not in (select pk from radius) But the second one is obviously not very fast. A "not in" never is... So I now do things just a little bit different. I added a field to the table radiusupdate called "newline". It is default set to true. Then I replace the second query by these two: update radiusupdate set newline = false from radius R where radiusupdate.pk = radius.pk insert into radius select * from radiusupdate RU where newline = true This is a lot faster in my case Reinoud ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] optimizer question
Hi,
I have a table that contains almost 8 milion rows. The primary key is a
sequence, so the index should have a good distribution. Why does the
optimizer refuse to use the index for getting the maximum value?
(even after a vacuum analyze of the table)
radius=# explain select max(radiuspk) from radius ;
NOTICE: QUERY PLAN:
Aggregate (cost=257484.70..257484.70 rows=1 width=8)
-> Seq Scan on radius (cost=0.00..237616.76 rows=7947176 width=8)
Table and key info:
Did not find any relation named "radius_pk".
radius=# \d radius
Table "radius"
Attribute | Type | Modifier
-+--+---
sessionid | character varying(30)| not null
username| character varying(30)| not null
nas_ip | character varying(50)| not null
logfileid | integer |
login_ip_host | character varying(50)| not null
framed_ip_address | character varying(50)|
file_timestamp | timestamp with time zone | not null
corrected_timestamp | timestamp with time zone | not null
acct_status_type| smallint | not null
bytesin | bigint |
bytesout| bigint |
handled | boolean | not null default 'f'
sessionhandled | boolean | not null default 'f'
radiuspk| bigint | not null default nextval
('radiuspk_seq'::text)
Indices: pk_radius,
radius_us
radius=# \d pk_radius
Index "pk_radius"
Attribute | Type
---+
radiuspk | bigint
unique btree (primary key)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] performance question
> On Tue, 28 Aug 2001, Reinoud van Leeuwen wrote:
>
>> Can somebody explain to me:
>>
>> > radius=# explain select count (radiuspk) from radius ;
>> > NOTICE: QUERY PLAN:
>> >
>> > Aggregate (cost=12839.79..12839.79 rows=1 width=8)
>> > -> Seq Scan on radius (cost=0.00..11843.43 rows=398543 width=8)
>> >
>> > EXPLAIN
>>
>>
>> This query answers me *instantly* after hitting return
>>
>> > radius=# select count (radiuspk) from radius ;
>> > count
>> >
>> > 398543
>> > (1 row)
>>
>> This query takes about 3 seconds. But the query plan *already* knows
>> the number of rows ("rows=398543"). So why does it take 3 seconds. Is
>> my assumption correct that the optimiser still can be optimized a
>> little? :-)
>
> Not in this case. The row numbers from explain are just estimates
> from the last vacuum. As you modify the table, the estimated rows will
> be off.
Yes, I just found out that somebody else is running a script on our test
server that vacuums all databases each night. That explains a lot.
Thanx for thinking with me
Reinoud
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[HACKERS] performance question
Can somebody explain to me:
> radius=# explain select count (radiuspk) from radius ;
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=12839.79..12839.79 rows=1 width=8)
> -> Seq Scan on radius (cost=0.00..11843.43 rows=398543 width=8)
>
> EXPLAIN
This query answers me *instantly* after hitting return
> radius=# select count (radiuspk) from radius ;
> count
>
> 398543
> (1 row)
This query takes about 3 seconds. But the query plan *already* knows the
number of rows ("rows=398543"). So why does it take 3 seconds. Is my
assumption correct that the optimiser still can be optimized a little? :-)
Reinoud (not that this is a real problem, just wondering)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Link to bug webpage
> On Tue, 21 Aug 2001, Lamar Owen wrote: [...] > > What who thinks of what has actually become irrelevant. The following > is clear: > > o No tool will replace the mailing lists > o The mailing lists are where discussion will be held > o Many/most maintainers have no desire to update bug reports disadvantages of a mailinglist: - easy problems are solved by 10 people in 5 minutes, hard ones often by none - not clear who is the "owner" of a problem OK so what we need is an enhaced mailinglist with a web interface. I've used wreq (http://www.math.duke.edu/~yu/wreq/) in the past for something similar. Features: - web and mail interface - each problem gets an assigned owner - status of entered items is clear - not much extra work in comparison to a mailinglist. - outstanding bugs stay visible until closed (instead of forgotten) It may not be ideal for this kind of thing, but it is a start. Has anyone suggestions for a better tool? Reinoud ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] plpgsql: Checking status on a 'INSERT INTO ...'
> I'm porting some stored procedures from a MSSQL server, and thought I'd > use PL/pgSQL. > > The original code is checking the insert with the line: > >if (@@Error != 0) You might want to use something like: SELECT INTO variable_name * FROM table WHERE field = some_value; IF FOUND THEN somevar := variable_name.fieldname ; ELSE RAISE EXCEPTION ''ERROR blah blah''; END IF; And you also want to look into the @@rowcount: GET DIAGNOSTICS v_rowcount = ROW_COUNT ; Reinoud ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: AW: [HACKERS] functions returning records
> >> For the result from foo() you must somewhere define attributes >> (names). >> Where? In CREATE FUNCTION statement? Possible must be: >> >> select name1, name2 from foo() where name1 > 10; > > Yes, optimal would imho also be if the foo() somehow had access to the > where restriction, so it could only produce output, that the > higher level is interested in, very cool. This would be extremely > useful for me. Very hard to implement, or even find an appropriate > interface for though. You could easily implement it *in* the function foo IMHO. Since the function does some black magic to create the result set to begin with, you can change it to use parameters: select name1, name2 from foo(10, NULL, NULL) where name1 > 10; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] functions returning records
On Tue, 26 Jun 2001 17:11:47 -0400 (EDT), you wrote: >I started thinking about Tom's idea to implement functions as table >source. > >To me, it seems that a very few changes are necessary: >a) parser must be changed to allow functioncall to be a table_ref >(easy) > >b) when a Query node is generated out of such a call "select * from foo()" >it should be almost identical to one generated out of "select * from >(select * from foo)" with one distinction: list of query attributes should >be completed based on return type of foo(). > >c) executor should support execution of such Query node, properly >extracting things out of function's return value and placing them into >result attributes. Coming from a Sybase environment I would love to have functions return a result set. A few things to think of: 1: will it be possible to return multiple result sets? (in Sybase any select statement that is not redirected to variables or a table goes to the client, so it is quite common to do multiple selects). Does the postgresql client library support this? 2: will it be possible to put a single result set in a table. Something like "resultfunction (argument) INTO TABLENAME" or "INSERT INTO TABLENAME resultfunction(argument) -- __________ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] POSTMASTER
On 18 Jun 2001 17:00:41 -, you wrote: > >Hello All. > >How can i limit how much of cpu the postmaster can use? Maybe your host OS can limit the resource usage of the userid that postmaster runs under? -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: AW: AW: [HACKERS] Postgres Replication
On Tue, 12 Jun 2001 15:50:09 +0200, you wrote: > >> Here are some disadvantages to using a "trigger based" approach: >> >> 1) Triggers simply transfer individual data items when they >> are modified, they do not keep track of transactions. >> 2) The execution of triggers within a database imposes a performance >> overhead to that database. >> 3) Triggers require careful management by database administrators. >> Someone needs to keep track of all the "alarms" going off. >> 4) The activation of triggers in a database cannot be easily >> rolled back or undone. > >Yes, points 2 and 3 are a given, although point 2 buys you the functionality >of transparent locking across all involved db servers. >Points 1 and 4 are only the case for a trigger mechanism that does >not use remote connection and 2-phase commit. > >Imho an implementation that opens a separate client connection to the >replication target is only suited for async replication, and for that a WAL >based solution would probably impose less overhead. Well as I read back the thread I see 2 different approaches to replication: 1: tight integrated replication. pro: - bi-directional (or multidirectional): updates are possible everywhere - A cluster of servers allways has the same state. - it does not matter to which server you connect con: - network between servers will be a bottleneck, especially if it is a WAN connection - only full replication possible - what happens if one server is down? (or the network between) are commits still possible 2: async replication pro: - long distance possible - no problems with network outages - only changes are replicated, selects do not have impact - no locking issues accross servers - partial replication possible (many->one (datawarehouse), or one-many (queries possible everywhere, updates only central) - goof for failover situations (backup server is standing by) con: - bidirectional replication hard to set up (you'll have to implement conflict resolution according to your business rules) - different servers are not guaranteed to be in the same state. I can think of some scenarios where I would definitely want to *choose* one of the options. A load-balanced web environment would likely want the first option, but synchronizing offices in different continents might not work with 2-phase commit over the network And we have not even started talking about *managing* replicated environments. A lot of fail-over scenarios stop planning after the backup host has take control. But how to get back? -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Migration from FoxPro
On Tue, 12 Jun 2001 13:36:02 -0400, you wrote: >Anyone know of any alternatives to using pgAdmin to migrate a database >(schema and data) from Foxpro to PostgreSQL? pgAdmin worked fine on my >initial test database, but it was slow... very slow. I'd like to try to >migrate one of our production databases, where several tables have >200,000+ records each. I can export the data into a PG COPY friendly >format, but was wondering if anyone knew of a tool that would migrate >all of the primary keys and indexes too? Or a perl utility that would >read the Foxpro database and generate the SQL commands needed to create >the tables and indexes? > (assuming you use some windows version :-) - Download the Powerdesigner 45 day trial from www.sybase.com - make an ODBC connection to your database - reverse engineer the datamodel - change the target model to postgresql - install postgresql odbc drivers - generate a database creation script and execute it through the ODBC connection - create a report of your database for your documentation :-) -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Postgres Replication
ot;stable device"), until it is sure it has reached its final destination - a replication server can send data to another replication server in a compact (read: WAN friendly) way. A chain of replication servers can be made, depending on network architecture) - the final replication server makes a almost standard client connection to the target database and translates the compact transactions back to SQL statements. By using masks, extra functionality can be built in. This kind of architecture has several advantages: - only committed transactions are replicated which saves overhead - it does not have very much impact on performance of the source server (apart from reading the WAL) - since every replication server has a stable device, data is stored when the network is down and nothing gets lost (nor stops performing) - because only the log reader and the connection from the final replication server are RDBMS specific, it is possible to replicate from MS to Oracle using a Sybase replication server (or different versions etc). I do not know how much of this is patented or copyrighted, but the architecture seems elegant and robust to me. I have done implementations of bi-directional replication too. It *is* possible but does require some funky setup and maintenance. (but it is better that letting offices on different continents working on the same database :-) just my 2 EURO cts :-) -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen [EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] capturing stored procedure return values from php??please help .
> > hi, > > i am developing an application using php and postgresql. i do not > know how to capture the return values of functions (stored > procedures) from php. > select myfunction(parameter) ; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ORDER BY Problem...
> Hello... > > Why does Postgresql order the uppercase letters first? > > I have e.g. a table with one row an in this row there are follow > values: > > row1 > > ADC > aa > ABC > > With this select-syntax > > select * from table order by row1 > > I become this output > > ABC > ADC > aa > > but I want this ouptut: > > aa > ABC > ADC > > What do I wrong? This will not solve your problem, but a way around this is to sort on upper (row1): # select * from test order by col1; col1 -- ABCD AD Abc (3 rows) # select * from test order by upper(col1); col1 -- Abc ABCD AD (3 rows) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
