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 variable
I'm debugging a trigger in plpgsql and for some reason or the select into var 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: NULL 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: quote source=Postgresql 7.2 User Manual, chapter 6 near the end Arrays are not sets; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. /quote 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: quote source=Postgresql 7.2 User Manual, chapter 6 near the end Arrays are not sets; using arrays in the manner described in the previous paragraph is often a sign of database misdesign. /quote 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
[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] 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
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: [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
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] 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]
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
