Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
[EMAIL PROTECTED] wrote: One of my friend lost data with mysql yesterday.. The machine was taken down for disk upgrade and mysql apperantly did not commit the last insert.. OK he was using myisam but still..:-) It sounds like that is more a problem with improper operating protocols than with the underlying database. No. Problem is machine was shutdown with shutdown -h. It sends sigterm to everybody. A good process would flsuh the buffers to disk before finishing. Mysql didn't on that occasion. Transactions or not, this behaviour is unacceptable for any serious app. Would PG know enough to do a commit regardless of how the database was shut down? A second question is whether doing a commit is what the user or application would always want to have happen, as it could result in a half-completed transaction. Do a shutdown -h on a live database machine with pg. It will gracefully shut itself down. Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] int1?
On Thu, 2003-10-09 at 02:16, CSN wrote: Is there any date type that can be used for 0-255 values? Like an int1 or byte column. An int2 with a constraint on it. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA Fear the Penguin!! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Cross database foreign key workaround?
You can try to use dblink (function returning results from a remote database)and create some triggers with it in order to make remote referential integrity. Or if there's a lot of links between the tables in the 2 databases it may be better to use one database. --- David Busby [EMAIL PROTECTED] wrote: List, What are the recommended work arounds for cross database foreign keys? As I understand it transactions are not atomic with the TCL method. I have a situation that requires a master database and then a separate database for every subscriber. Subscribers need read/write to both databases. I chose separate databases because there are 20+ large tables that would require uid/gid columns, indexes and where conditions to separate information by subscriber. I thought that was too much overhead. Should I just use my application to make changes and ensure references that need to take place across databases? Or should I add a uid/gid to all necessary tables, create indexes and update all necessary where clauses? Ideas? /B ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Does postgresql support HKSCS ?
Dennis Gearon wrote: Isn't HKSC one of the languages supported by UNICODE? I thought they had every langauge past and present in it. You'll have to map HKSCS to UNICODE yourself before the data hits postgresql, and data out of postgresql would be in UNICODE, and up to you to convert back to HKSCS after retrival. pgsql doesn't have *native* HKSCS support. HKSCS is an extension of Big5 right? From the info we have: http://www.unicode.org/faq/unicode_iso.html http://www.info.gov.hk/digital21/eng/hkscs/download/big5-iso.txt We might be able to duplicate Big5's code in pgsql to support HKSCS natively. I don't have experience with HKSCS nor test materials to attempt it though. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 4:00pm up 287 days, 7:33, 8 users, load average: 6.32, 6.34, 6.18 pgp0.pgp Description: PGP signature
[GENERAL] int1?
Is there any date type that can be used for 0-255 values? Like an int1 or byte column. CSN __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] autoupdate sequences after copy
On Thursday 09 October 2003 08:10, CSN wrote: Is there a way to have p/k sequences get automatically set to max(id)+1 after COPY's like the following? copy table1 (id,name) from stdin; 1 abc 2 def 3 fhi \. Not really - if you don't use the sequence it keeps its value. If you look at pg_dump it issues an explicit setval() after a copy. I'm not sure you can even work around it with a BEFORE trigger to check and update the sequence, the nextval() call will probably be processed before the trigger gets called (haven't checked). In any case, performance would be a bit poor. Is there any reason why you're supplying your own id values when you already have a sequence? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PL/PGSQL for permutations?
Joe Conway wrote:
D. Stimits wrote:
table field pair. E.G., if I had in table 'one':
left right
=
a b
a c
b d
...then I'd need a list of a, b, c, d, and produce a new table:
left right
=
a b
a c
a d
b a
b c
b d
c a
c b
c d
d a
d b
d c
I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if
possible anyway), but why not:
create table t1(f1 text, f2 text);
insert into t1 values('a','b');
insert into t1 values('a','c');
insert into t1 values('b','d ');
select a, b
from
(select distinct f1 as a from t1 union select distinct f2 from t1)
as ss1,
(select distinct f1 as b from t1 union select distinct f2 from t1)
as ss2
where ss1.a != ss2.b;
a | b
+
a | b
a | c
a | d
b | a
b | c
b | d
c | a
c | b
c | d
d | a
d | b
d | c
(12 rows)
This worked quite well, thank you! I'm still in need though of learning
more about PL/PGSQL, as I have other programming to add (well, I could
do this in C as a PostgreSQL C function, but I want to stick to PL/PGSQL
for the moment). I'm still looking for a non-trivial, in-depth, full
reference to PL/PGSQL. I've found many good introductory or tutorial
type web pages, but not a full and complete reference to PL/PGSQL. The
permutations were themselves the easy part, now each permutation has to
do some non-trivial combinatorics on trigger whenever a change is made.
D. Stimits
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Slow SELECT
Mat wrote: Lines from postgresql.conf that don't start with a '#': tcpip_socket = true shared_buffers = 126976 #992 MB sort_mem = 36864#36 MB vacuum_mem = 73696 #72 MB I would suggest scale down shared buffers to 128 or 64MB and set effective cache size correct. That should help. HTH Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] help with large objects in 7.3.4
Greetings, I am trying to create a database to store imges as large objects. I am using postgres7.3.4 on a SuSE8.2 machine. I read the PostgreSQL documentation on lage objects. There are some new fnctions which I am unfamilar with. I have books on postgresql but I am not aware of any current book with example usage of these functions. I previously used up to 7.2 which had lo_import, lo_export and lo_unlink. I would be grateful for example usage in psql of the following functions (seen in postgresql7.3.4: lo_create, lo_import lo_export lo_import lo_open lo_write lo_read lo_seek Help would be appreciated. Yours sincerely Sibu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Response from MySql AB (Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing))
Here's an interesting response from mysql.com sales. Frankly, I don't see how using it on multiple internal servers violates the GPL?!?: btw, forgive me if this comes through as a repost later. I'm still getting used to having multiple email addrs in this client :) Hi John, Thank you for your interest in MySQL. My answers below. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 7:50 AM To: [EMAIL PROTECTED] Subject: MySQL Licensing Question, US, -unknown- The following form was submitted via MySQL.com feedback engine: Name: John Wells Email address: [EMAIL PROTECTED] Type of interest: for company use Country: US Support contract: no Enter Your Questions, Comments, Feedback or Your Message here: I'm a little confused on the meaning of distributing either externally or internally on the license page. If we develop an application that uses MySql as the backend database and use in on our internal LAN, do we need to license it commmercially? Internal distribution applies to internally distributing/installing more than one application. If you plan to use one installation of MySQL it would not be considered distribution and you could use if for free with your internally built application. How would this be in violation of the GPL? If your application (internal or external) is licensed under the terms of the GPL, you are allowed to distribute MySQL, also under the GPL, for free. What do you mean, exactly, by internal distribution? Also, if we write an application that expects MySql (like a web app), but we don't package the database with the application and tell the customer that they must download and install it, is this violation of the GPL? Again, if your application is licensed under the terms of the GPL you can use MySQL for free. If your application is not licensed under the terms of the GPL, commercial non-GPL licenses are necessary. You will need to purchase licenses under this scenario (unless you go GPL), please review our licensing examples, http://www.mysql.com/products/licensing-examples.html. How are you planning to use MySQL? Mr. Matt Fredrickson, Sales Executive MySQL, Inc. - www.mysql.com Office: +1 425 390 0154 Fax: +1 425 663 2001 MySQL Featured in the Wall Street Journal: http://webreprints.djreprints.com/785490482991.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] int1?
CSN wrote: Is there any date type that can be used for 0-255 values? Like an int1 or byte column. You can use a smallint with constraint. HTH Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Locale bug?
Here is a simplificated example: CREATE OR REPLACE FUNCTION ttt () RETURNS text AS ' return ; ' LANGUAGE 'plperlu'; CREATE OR REPLACE FUNCTION qqq () RETURNS text as ' DECLARE v_text text; v_text2 text; BEGIN v_text := upper(); -- cyrillic chars v_text2 := ttt(); RETURN v_text || upper(); -- cyrillic chars END; ' LANGUAGE 'plpgsql'; and qqq() returns . That is upper() doesn't work correctly after plperlu function call. -- Sergey Suleymanov ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] suggestions for tracking down syntax errors?
I created a schema and piped it to psql but got an error message: ~/hacks/psql $ cat create.sql | psql test ERROR: table country does not exist ERROR: parser: parse error at or near ) at character 91 ERROR: table customer does not exist NOTICE: CREATE TABLE will create implicit sequence 'customer_customer_id_seq' for SERIAL column 'customer.customer_id' But I don't know exactly where character 91 is... a line number (including the comments in the file) would be more useful to me. I can sort of triangualate where the problem with the parenthesis is by noting where the DROP TABLE stmts failed, but that leaves two parenthetic expressions to examine for correctness. Here is the relevant part of the file that I piped to psql: /*==*/ /* Project Filename:C:\Program Files\Datanamic\DeZign for Databases V3\sample models\licenses.dez*/ /* Project Name:*/ /* Author: */ /* DBMS:PostgreSQL 7*/ /* Copyright: */ /* Generated on:10/7/2003 5:24:50 PM*/ /*==*/ /*==*/ /* Tables */ /*==*/ DROP TABLE country; CREATE TABLE country ( country_id VARCHAR(3) PRIMARY KEY, country VARCHAR(80), ); DROP TABLE customer; CREATE TABLEcustomer ( customer_id SERIAL PRIMARY KEY, country_id VARCHAR(3) REFERENCES country(country_id), name VARCHAR(100) NOT NULL, companyname VARCHAR(100) NOT NULL, address1 VARCHAR(100) NOT NULL, ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
MySQL is not free software. Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Wed, 2003-10-08 at 16:23, Joshua D. Drake wrote: Here is the simple thing about MySQL licensing. It is GPL. If you modify the mySQL source or you link a proprietary app to mySQL without a commercial license. You must distrubute your changes and or application as GPL or GPL compatibile. You have two contradictory statements here, which unfortunately represent the internal contradictions in MySQL's license (at least, those versions after version 3.23.19, when MySQL AB adopted the current licensing scheme). Certainly, if MySQL is licensed under the GPL, you must distribute or make available source code to any changed version of MySQL that you distribute, or any other derivative works of MySQL that you distribute. However, MySQL's stated license makes far greater requirements on those who use MySQL. Even though many distributors of MySQL, including the normally very license-conscious Debian GNU/Linux, include only the GPL as its license, there are in fact additional constraints which limit the rights that are given by the GPL. MySQL AB's license information web page [1] includes in plain language what their intent is, and that intent is not the GPL, nor is it compatible with the GPL. The non-commercial (free-of-charge) MySQL license extends the requirement to make available source code to your application, regardless of whether or not your application is a derived work of MySQL. All practical interpretations of the GPL, including the FSF's, exclude from the requirement to distribute source code any works that are collected by simple aggregation, meaning they are present on the same distribution medium or in the same distribution package as the licensed work, but are not related to the licensed work by the sharing of licensed components. MySQL does not distinguish between derivative works of MySQL and those that are collected along with it by simple aggregation. So, for example, if I wish to sell a version of Debian with a proprietary, closed-source installation tool (which does not use or relate to MySQL in any way) and I wish to also include MySQL and its source code in my distribution, I am required to get a commercial license from MySQL. That is not consistent with the terms of the GPL under which I received MySQL from Debian. I don't know how to put it more plainly than that. Even though MySQL AB claims that their product is licensed under the GPL, it is not, because they put significant additional license terms on it that remove some rights given by the GPL. The overall license terms of MySQL do not meet any standard of Free software licenses that I know, including the Debian Free Software Guidelines [2]. I believe that Debian and other GNU/Linux distributions should move MySQL to their non-free sections, along with other software that is free for non-commercial use. The consequences for any commercial enterprise using MySQL in any way must be very closely examined, and certainly aren't obvious in the way that the consequences of the GPL are obvious. Thanks, Bill Gribble [1] http://www.mysql.com/products/licensing.html [2] http://www.debian.org/social_contract#guidelines ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Domains (was Re: [GENERAL] int1?)
On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
http://www.postgresql.org/docs/7.3/static/sql-createdomain.html
CREATE DOMAIN domainname [AS] data_type
[ DEFAULT default_expr ]
[ constraint [, ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL }
test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint
between 0 and 255);
ERROR: DefineDomain: CHECK Constraints not supported
So, how would I create a domain that limits a smallint?
You need to use 7.4. In 7.3 you couldn't use check constraints with domains.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] autoupdate sequences after copy
I just run a script to update them after importing data. Something like this... (not
a real script...)
while read tablename
do
echo select setval('${tablename}_${tablename}_seq', \
(select max(${tablename}id) from $tablename)) | psql database
done
tablenames.txt
Of course, this assumes you allowed the default sequence names to be created via
SERIAL and that you created the primary keys as tablenameid. You might need a text
file with table, key, and sequence names, but this is likely easier than issuing a
bunch of psql commands by hand.
Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
[EMAIL PROTECTED]
Phone: (253) 798-3549
Pager: (253) 754-0002
CSN [EMAIL PROTECTED] 10/09/03 12:10PM
On Thursday 09 October 2003 08:10, CSN wrote:
Is there a way to have p/k sequences get
automatically
set to max(id)+1 after COPY's like the following?
copy table1 (id,name) from stdin;
1 abc
2 def
3 fhi
\.
Not really - if you don't use the sequence it keeps
its value. If you look at
pg_dump it issues an explicit setval() after a copy.
I'm not sure you can even work around it with a BEFORE
trigger to check and
update the sequence, the nextval() call will probably
be processed before the
trigger gets called (haven't checked). In any case,
performance would be a
bit poor.
Is there any reason why you're supplying your own id
values when you already
have a sequence?
--
Richard Huxton
Archonet Ltd
Is there any reason why you're supplying your own id
values when you already have a sequence?
I'm importing a lot of data and tables (from mysql)
and want to keep the ID's the same.
CSN
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
---(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
---(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: Domains (was Re: [GENERAL] int1?)
On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
http://www.postgresql.org/docs/7.3/static/sql-createdomain.html
CREATE DOMAIN domainname [AS] data_type
[ DEFAULT default_expr ]
[ constraint [, ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL }
test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint
between 0 and 255);
ERROR: DefineDomain: CHECK Constraints not supported
So, how would I create a domain that limits a smallint?
You need to use 7.4. In 7.3 you couldn't use check constraints with domains.
So is there a documentation bug, or, what kind of constraints
can be placed on domains besides { NOT NULL | NULL }?
--
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA
Causation does NOT equal correlation
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A consultant my company hired before bringing me in is pushing hard for MySql, citing speed and community support, as well as ACID support. Does the consultant push speed AND ACID or speed OR ACID? My point is that PostgreSQL is said to be harder to install/maintain/tune than MySQL. I have been reading some MySQL mailing list and for what I see there, using InnoDB tables (the only way to have foreign keys, transactions, and row level locking for MySQL) makes MySQL slower and adds complexity to tuning the database. See this thread for example http://lists.mysql.com/mysql/148832 . So when someone says that PostgreSQL without tuning is 5 times slower than MySQL retrieving the same query, it is quite right to also say that MySQL InnoDB without tuning is 5 times slower than MySQL MyISAM. In my opinion you might consider MySQL only when you don't need the features provided by PostgreSQL (and even then data consistency and durability issues favor PostgreSQL) because if you need them, your developers need to implement them and do extra work, spending more time and money. It was already mentioned but I'll post this link again http://sql-info.de/mysql/ . IMHO if you are not aware of these gotchas they can also increase development time because some things are too different from regular/logical behavior (or common sense if you will) of any other database. Kaarel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Parent Id
On Thu, Oct 09, 2003 at 15:55:27 -0400,
Gene Vital [EMAIL PROTECTED] wrote:
have an example :)
Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 14:26:21 -0400,
Gene Vital [EMAIL PROTECTED] wrote:
when inserting new records into parent / child tables, what is the best
recommended way of retrieving the pkey value from the parent table when
using auto incrementing fields ?
You want to use currval. currval will return the last value obtained
by nextval in the same session, so it is safe to use without any additional
locking.
You would do something like:
insert into parent_table (pk, col1, col2) values (default, 'val1', 'val2');
insert into child_table (pk, parent, col1, col2)
values (default, currval('parent_table_pk_seq'), 'val3', 'val4');
I don't remember when being able to use default in insert statements was
added. You may need to just leave the pk columns off the list. I added them
so that you could see what the normal sequence name looks like. I also
assume that the two pk columns are declared to be of type serial. If not,
then you have to do the sequence and default creation yourself.
---(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: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Thu, Oct 09, 2003 at 01:33:39PM +0200, Harald Fuchs wrote: In article [EMAIL PROTECTED], Karel Zak [EMAIL PROTECTED] writes: BTW, MySQL versions without transactions are unusable for 365/7/24 systems, because you cannot make backup of DB without transaction or redo log. Huh? People have backed up their MyISAM tables with mysqlhotcopy or something like that for ages. This tool locks all tables and can take ^ :-) a significant amount of time for large databases. Since many people don't want that, they just create a small replication slave used exclusively for backup. How sure you with integrity of backup without transaction? For example references between tables... Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Domains (was Re: [GENERAL] int1?)
On Thu, Oct 09, 2003 at 14:46:08 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
http://www.postgresql.org/docs/7.3/static/sql-createdomain.html
CREATE DOMAIN domainname [AS] data_type
[ DEFAULT default_expr ]
[ constraint [, ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL }
test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK
(smallint between 0 and 255);
ERROR: DefineDomain: CHECK Constraints not supported
So, how would I create a domain that limits a smallint?
You need to use 7.4. In 7.3 you couldn't use check constraints with domains.
So is there a documentation bug, or, what kind of constraints
can be placed on domains besides { NOT NULL | NULL }?
I think the documentation is correct. As I read it it says that only NOT NULL
and NULL constraints are allowed. This is easy to overlook. I know I got
caught by this when I tried it.
I started using 7.4 pretty early on since I wanted to use check constraints
in earthdistance to have a domain that represented points on the surface of
the earth on top of the cube data type.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL query problem
fine). PG returns: ERROR: Relation _con does not exist This is my query: SELECT _CON.con_id, Please make sure you get the quoting right regarding table names. PostgreSQL will fold _CON into _con unless quoted _CON. So, it may be that you created the table with quotes (_CON). Now, in your query you don't use quotes and thusly it is looking for a _con table. The simple rule of thumb is to either always or never use quotes. HTH, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] question about a select
OK, I have the following table: create table citations_by_level ( aid smallint, wid smallint, v_level varchar(50), w_level varchar(50), x_level varchar(50), y_level varchar(50), z_level varchar(50), byteloc integer ); (If it helps, aid/wid identifies a text or work, the levels are citation levels for that work (all but z_level potentially optional); eg for some work y_level might indicate chapters [z_levels indicate lines], and byteloc is the file position of that particular citation in the work). What I would *like* to be able to do is construct a query that groups by a level, but sorts by byteloc. I don't seem to be able to do this. Here are some examples. Note that y_level (any level) may have duplicates (which I want to eliminate), and that it's ordering is strictly on byteloc, not on its own value. postgres doesn't seem to have envisioned this scenario and/or I'm not being creative enough in constructing the query... Text=# select distinct on (y_level) y_level from citations_by_level where aid=543 and wid=1; y_level - 1 10 10a 11 12 13 14 15 16 17 18 19 2 20 21 22 23 24 25 26 27 28 29 2a 3 30 31 32 33 34 35 36 37 4 5 5a 6 7 7,8 8 9 t (42 rows) but as you can see, the ordering winds up being alphabetic on y_level which simply does not do. [In this case it is only coincidental that y_level appears numeric, it is a string and could be anything; and the 7,8 is such an example]. The *byteloc* associated with a given y_level (the location of that particular citation) does, but I can't seem to use it: Text=# select distinct on (y_level) y_level, byteloc from citations_by_level where aid=543 and wid=1 order by byteloc; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions Text=# select distinct on (y_level) y_level, byteloc from citations_by_level where aid=543 and wid=1 order by byteloc, y_level; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions ??? --Cindy -- [EMAIL PROTECTED] ---(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: [GENERAL] Parent Id
ok, I am new to Postgres so could you give a little better explanation
of this ??
I haven't created any sequence for this I am just using a type serial
field. will I have to create a sequence for it?
Here is my code to create the tables
CREATE TABLE workstations (station_id INT4 PRIMARY KEY, name
VARCHAR(50), description VARCHAR(250))
CREATE TABLE wsoptions (option_id SERIAL PRIMARY KEY, station_id INT4
REFERENCES workstations (station_id) ON DELETE CASCADE, type
VARCHAR(20), data TEXT)
insert into workstations (name, description)
values(new, This is a test)
insert into wsoptions (stations_id, type, data)
values( , LOCATION, 10th floor outer, office 27)
thanks
Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 15:55:27 -0400,
Gene Vital [EMAIL PROTECTED] wrote:
have an example :)
Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 14:26:21 -0400,
Gene Vital [EMAIL PROTECTED] wrote:
when inserting new records into parent / child tables, what is the best
recommended way of retrieving the pkey value from the parent table when
using auto incrementing fields ?
You want to use currval. currval will return the last value obtained
by nextval in the same session, so it is safe to use without any additional
locking.
You would do something like:
insert into parent_table (pk, col1, col2) values (default, 'val1', 'val2');
insert into child_table (pk, parent, col1, col2)
values (default, currval('parent_table_pk_seq'), 'val3', 'val4');
I don't remember when being able to use default in insert statements was
added. You may need to just leave the pk columns off the list. I added them
so that you could see what the normal sequence name looks like. I also
assume that the two pk columns are declared to be of type serial. If not,
then you have to do the sequence and default creation yourself.
--
Eugene Vital
Any technology indistinguishable from magic is insufficiently advanced.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] int1?
Is there any date type that can be used for 0-255 values? Like an int1 or byte column. A SMALLINT is two bytes on disk, use char instead. This is a hidden goodie in PostgreSQL and one that I wish was exposed via a more conventional syntax (*hint hint*). http://developer.postgresql.org/docs/postgres/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE -sc -- Sean Chittenden ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] autoupdate sequences after copy
Is there a way to have p/k sequences get automatically set to max(id)+1 after COPY's like the following? copy table1 (id,name) from stdin; 1 abc 2 def 3 fhi \. CSN __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: Domains (was Re: [GENERAL] int1?)
On Thu, 2003-10-09 at 15:13, Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 14:46:08 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
On Thu, 2003-10-09 at 14:46, Bruno Wolff III wrote:
On Thu, Oct 09, 2003 at 14:28:57 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
http://www.postgresql.org/docs/7.3/static/sql-createdomain.html
CREATE DOMAIN domainname [AS] data_type
[ DEFAULT default_expr ]
[ constraint [, ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL }
test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK
(smallint between 0 and 255);
ERROR: DefineDomain: CHECK Constraints not supported
So, how would I create a domain that limits a smallint?
You need to use 7.4. In 7.3 you couldn't use check constraints with domains.
So is there a documentation bug, or, what kind of constraints
can be placed on domains besides { NOT NULL | NULL }?
I think the documentation is correct. As I read it it says that only NOT NULL
and NULL constraints are allowed. This is easy to overlook. I know I got
caught by this when I tried it.
test1=# create domain foo as smallint not null;
CREATE DOMAIN
test1=# create domain bar as smallint CONSTRAINT wiggle not null;
CREATE DOMAIN
Oh, ok. Stuff in [] is not necessary. Still confusing.
--
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA
The difference between drunken sailors and Congressmen is that
drunken sailors spend their own money.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Response from MySql AB (Re: Humor me: Postgresql vs.
On Thu, 9 Oct 2003 [EMAIL PROTECTED] wrote: Andrew Sullivan [EMAIL PROTECTED] writes: On Thu, Oct 09, 2003 at 08:52:36AM -0600, scott.marlowe wrote: Fact: If you write your application to work with ODBC - MySQL connectivity, you can write a closed source app and sell it for money and Fact: nobody's ever tested any of this in court, so you're basically risking it. I think if people want legal advice about the status of MySQL's claims about GPL, they'd best consult a lawyer who knows a lot about software licenses. This is especially true considering the fact that the Free Software Foundation would *love* for MySQL AB to be right on this one. The interpretation of the GPL the FSF forwards is the one that they feel that they can safely defend in a court of law. However, if there was a precedent set for MySQL AB's interpretation that would suit them right down to the ground. 'K, you lost me here ... from what I've seen, MySQL AB's license is GPL with exceptions that force you to use our commercial version ... whereas my understanding of the GPL itself is that there are no exceptions, period ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] undefined reference to 'pg_detoast_datum'
In PostgreSQL 7.2 (Redhat 7.3 version, so it is patched), I'm trying to create a Version-1 C server extension. The Version-0 format works, the Version-1 version fails with: undefined reference to 'pg_detoast_datum' According to docs at: http://www.postgresql.org/docs/7.2/interactive/xfunc-c.html ...by using Version-1 the pg_detoast_datum is no longer needed. FYI, the function being created takes and returns a text argument. Are the docs there wrong, or is there a bug in the library build, that causes Version-1 to require linking to a lib with pg_detoast_datum? A google search shows very little concerning the pg_detoast_datum undefined reference link error. It is looking more like the V1 has to be skipped and I'll have to go back to V0 if I can't get this to work. [and unless someone can magically make the default PostgreSQL version on RH 7.3 change to a newer version of PostgreSQL, then a newer version can't be used] D. Stimits ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] question on setof record returning plpgsql function
Julie May wrote:
What I would like to do is also return the date that is assigned to
d_date for the current iteration of the first loop. The following
code does not work. I either get one of three error messages
depending on how many quote marks I use (unterminated string, error
with $1, or unexpected return type). And suggestions would be greatly
appreciated.
I think you had it working when you got the unexpected return type
message, you were just calling it wrong. The data types must match
*exactly* including WITH/WITHOUT TIME ZONE. I often find it simpler when
working with date/time data types to use an explicit cast, e.g.:
create table load_info_table(delivery_date date, clean_weight float8,
dirty_weight float8);
insert into load_info_table values('01/01/2003',8,10);
insert into load_info_table values('01/01/2003',9,11);
insert into load_info_table values('01/01/2003',10,12);
insert into load_info_table values('01/01/2003',7,8);
insert into load_info_table values('01/02/2003',18,20);
insert into load_info_table values('01/02/2003',29,36);
insert into load_info_table values('01/02/2003',9,15);
-- Function: public.get_factory_ytd()
CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record
AS '
declare
ytd_record record;
d_date record;
begin
for d_date in select distinct delivery_date from load_info_table
order by delivery_date asc loop
for ytd_record in execute
''select sum(clean_weight)/sum(dirty_weight) as tare,
''||d_date.delivery_date|| ''::date from load_info_table where
delivery_date = ''||d_date.delivery_date|| loop
return next ytd_record ;
end loop;
end loop;
return;
end' LANGUAGE 'plpgsql' VOLATILE;
test=# select * from get_factory_ytd() as (tare float8, delivery_date date);
tare| delivery_date
---+---
0.829268292682927 | 2003-01-01
0.803571428571429 | 2003-01-02
(2 rows)
Note the ::date that I stuck in the function and how I specified
delivery_date as type date in the query.
HTH,
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] relation vs table...
I don't know what Postgres considers a relation and had no intention of creating one when piping my schema to it... I always DROP TABLE before CREATE TABLE, so here are the ERRORS emitted when building the database: 3:ERROR: table country does not exist 6:ERROR: table customer does not exist 11:ERROR: table product does not exist 15:ERROR: table license does not exist 19:ERROR: table enduser does not exist 24:ERROR: table orders does not exist 29:ERROR: table enduser_license does not exist 33:ERROR: table item does not exist 37:ERROR: Relation product_version does not exist 38:ERROR: table product_version does not exist 43:ERROR: table ordered_item does not exist but each and every one of these was created via CREATE TABLE I don't understand why Postgres thinks I am creating a relation _and_ I don't know what it considers a relation to be. Create stmts follow: /*==*/ /* Tables */ /*==*/ DROP TABLE country; CREATE TABLE country ( country_id VARCHAR(3) PRIMARY KEY, country VARCHAR(80) ); DROP TABLE customer; CREATE TABLEcustomer ( customer_id SERIAL PRIMARY KEY, country_id VARCHAR(3) REFERENCES country(country_id), name VARCHAR(100) NOT NULL, companyname VARCHAR(100) NOT NULL, address1 VARCHAR(100) NOT NULL, address2 VARCHAR(100) NOT NULL, city VARCHAR(100) NOT NULL, zipcode VARCHAR(10) NOT NULL, state VARCHAR(10) NOT NULL, phone VARCHAR(20), fax VARCHAR(20), email VARCHAR(100), vatid VARCHAR(20) ); DROP TABLE product; CREATE TABLEproduct ( product_id SERIAL PRIMARY KEY, productdesc VARCHAR(100) NOT NULL, productname VARCHAR(100) NOT NULL ); DROP TABLE license; CREATE TABLElicense ( license_id SERIAL PRIMARY KEY, lickey VARCHAR(80) NOT NULL, liccode VARCHAR(80) NOT NULL ); DROP TABLE enduser; CREATE TABLEenduser ( enduser_id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customer(customer_id), name VARCHAR(40), companyname VARCHAR(40) ); DROP TABLE orders; CREATE TABLE orders ( orders_id SERIAL PRIMARY KEY, country_id VARCHAR(3) REFERENCES country(country_id), customer_id INTEGER REFERENCES customer(customer_id), name VARCHAR(100) NOT NULL, companyname VARCHAR(100) NOT NULL, address1 VARCHAR(100) NOT NULL, address2 VARCHAR(100) NOT NULL, city VARCHAR(100) NOT NULL, state VARCHAR(10) NOT NULL, zipcode VARCHAR(10) NOT NULL, orderdate DATE, phone VARCHAR(20), fax VARCHAR(20), email VARCHAR(100), vatid VARCHAR(20), refno VARCHAR(100), promotion VARCHAR(100) ); DROP TABLE enduser_license; CREATE TABLE enduser_license ( license_id INTEGER REFERENCES license(license_id), enduser_id INTEGER REFERENCES enduser(enduser_id), PRIMARY KEY (license_id, enduser_id) ); DROP TABLE item; CREATE TABLE item ( item_id SERIAL, product_version_id INTEGER REFERENCES product_version(product_version_id), active INTEGER NOT NULL, itemname VARCHAR(100) NOT NULL, unitprice DECIMAL, numberoflic INTEGER, PRIMARY KEY (item_id, product_version_id) ); DROP TABLE product_version; CREATE TABLE product_version ( product_version_id SERIAL PRIMARY KEY, product_id INTEGER REFERENCES product(product_id) ); DROP TABLE ordered_item; CREATE TABLE ordered_item ( orders_id INTEGER REFERENCES orders(orders_id), item_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, product_version_id VARCHAR(10), unitprice DECIMAL, PRIMARY KEY (orders_id, item_id, product_id) ); /*==*/ /* Indexes */ /*==*/ /*==*/ /* Procedures */ /*==*/ /*==*/ /* Triggers*/ /*==*/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Thu, 9 Oct 2003 01:28, John Wells wrote: Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers. I'm preparing to enter a discussion with management at my company regarding going forward as either a MySql shop or a Postgresql shop. It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A consultant my company hired before bringing me in is pushing hard for MySql, citing speed and community support, as well as ACID support. Apologies for the empty reply - my mind is on brain death so I needed to imitate it. I'll address only the alleged community support for MySql because you will get much better qualified replies to your other concerns from others. About 18 months ago I had to choose a DB for my home grown small systems. Knowing very little about them I lurked on the both this list and a MySql list for a couple of months. In contrast to this list, the MySql one not only had a high proportion of brain dead questions, there were a fair few answers of the same grade too. I quickly decided that Postgresql was the better product by far for that and many other reasons. HTH Bob ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Domains (was Re: [GENERAL] int1?)
On Thu, 2003-10-09 at 12:54, Sean Chittenden wrote:
Is there any date type that can be used for 0-255 values? Like
an int1 or byte column.
A SMALLINT is two bytes on disk, use char instead. This is a hidden
However char has some serious deficiencies IIRC, such as the fact
that there's no int-char casts and it's standard I/O format is
characters. You can use ascii and chr to get around some of that,
but it's ugly.
*nods* I have explicit casts everywhere when dealing with char and
it's far from being elegant or clean.
goodie in PostgreSQL and one that I wish was exposed via a more
conventional syntax (*hint hint*).
If we were going to do that I think we'd be better off making a new
type and leaving char alone.
You won't hear any disagreements from me on this one. I've
sufficiently abused char as a 1 byte storage field and would love to
see an int1 or tinyint datatype added to cover this situation. -sc
http://www.postgresql.org/docs/7.3/static/sql-createdomain.html
CREATE DOMAIN domainname [AS] data_type
[ DEFAULT default_expr ]
[ constraint [, ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL }
test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint
between 0 and 255);
ERROR: DefineDomain: CHECK Constraints not supported
So, how would I create a domain that limits a smallint?
--
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA
You can either have software quality or you can have pointer
arithmetic, but you cannot have both at the same time.
Bertrand Meyer
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: RE : [GENERAL] Is the use of array as PL/PGSQL function arguments
Added to TODO: o Allow PL/pgSQL to handle %TYPE arrays, e.g. tab.col%TYPE[] --- Bruno BAGUETTE wrote: CREATE OR REPLACE FUNCTION myownfunction(members.id%TYPE[], events.id%TYPE) RETURNS BOOLEAN AS ' You can certainly use arrays as arguments, but I don't think you can combine %TYPE with [] like that. Thanks Tom for the information ! :-) When I create functions with %TYPE, PostgreSQL replies that it will convert the %TYPE with a VARCHAR (for example). So it could replace a %TYPE[] by a VARCHAR[] (in my example) ? It seems that this modification could be done easily, I will try to find in the source code to create this patch. (Without any warranties, I never done a patch for PostgreSQL). In the meantime, is it possible to add this feature in the TODO list ? Regards, --- Bruno BAGUETTE - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] process hangs using perl?
Running a perl script against my dev server (pg 7.3.4) that currently runs fine on the production server. And for some reason Pg always seems to hang during the process (see below for Top print out). This happens randomily throughout the script and can be on a SELECT, INSERT, or UPDATE statement, but doesn't cause the Pg server to go down (which is good). Any suggestions on where to start looking? Perl version 5.6.1 ---Top print out 4:08pm up 23 days, 4:46, 3 users, load average: 1.00, 1.05, 1.39 78 processes: 75 sleeping, 2 running, 1 zombie, 0 stopped CPU0 states: 30.4% user, 4.0% system, 0.0% nice, 64.4% idle CPU1 states: 47.0% user, 3.3% system, 0.0% nice, 49.0% idle Mem: 513640K av, 505332K used,8308K free, 0K shrd, 13176K buff Swap: 2044056K av, 48624K used, 1995432K free 356028K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 16094 postgres 14 0 98.8M 97M 18900 R85.2 19.4 31:38 postgres: phatcher mdc_oz [local] SELECT 16655 postgres 9 0 1052 1052 836 R 0.1 0.2 0:00 top 14397 postgres 8 0 1964 1840 1708 S 0.0 0.3 0:00 /usr/local/postgresql7.3.4/bin/postmaster 14398 postgres 9 0 2932 2780 1700 S 0.0 0.5 0:03 postgres: stats buffer process 14399 postgres 9 0 2168 2016 1704 S 0.0 0.3 0:05 postgres: stats collector process 16074 postgres 9 0 23836 20M 18884 S 0.0 4.1 16:24 postgres: phatcher mdc_oz [local] SELECT waiting 16076 postgres 9 0 21792 20M 18916 S 0.0 4.1 644:42 postgres: phatcher mdc_oz [local] SELECT waiting 16546 postgres 9 0 17988 16M 17208 S 0.0 3.3 0:11 postgres: phatcher mdc_oz [local] SELECT waiting 16650 postgres 9 0 1332 1332 1060 S 0.0 0.2 0:00 /bin/bash TIA Patrick Hatcher ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] question on setof record returning plpgsql function
Joe, Thank you very much. I didn't even think of casting the result of the first loop. I will test out the function tomorrow with the cast included and let you know how it works. From the looks of it (your results) it should work fine. Joe Conway Wrote: I think you had it working when you got the unexpected return type message, you were just calling it wrong. The data types must match *exactly* including WITH/WITHOUT TIME ZONE. I often find it simpler when working with date/time data types to use an explicit cast, e.g.: -- Function: public.get_factory_ytd() CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record AS ' declare ytd_record record; d_date record; begin for d_date in select distinct delivery_date from load_info_table order by delivery_date asc loop for ytd_record in execute ''select sum(clean_weight)/sum(dirty_weight) as tare, ''||d_date.delivery_date|| ''::date from load_info_table where delivery_date = ''||d_date.delivery_date|| loop return next ytd_record ; end loop; end loop; return; end' LANGUAGE 'plpgsql' VOLATILE; test=# select * from get_factory_ytd() as (tare float8, delivery_date date); tare| delivery_date ---+--- 0.829268292682927 | 2003-01-01 0.803571428571429 | 2003-01-02 (2 rows) Note the ::date that I stuck in the function and how I specified delivery_date as type date in the query. HTH, Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Thursday, October 9, 2003, at 01:42 AM, Shridhar Daithankar wrote: [EMAIL PROTECTED] wrote: One of my friend lost data with mysql yesterday.. The machine was taken down for disk upgrade and mysql apperantly did not commit the last insert.. OK he was using myisam but still..:-) It sounds like that is more a problem with improper operating protocols than with the underlying database. No. Problem is machine was shutdown with shutdown -h. It sends sigterm to everybody. A good process would flsuh the buffers to disk before finishing. Mysql didn't on that occasion. Transactions or not, this behaviour is unacceptable for any serious app. Would PG know enough to do a commit regardless of how the database was shut down? A second question is whether doing a commit is what the user or application would always want to have happen, as it could result in a half-completed transaction. Do a shutdown -h on a live database machine with pg. It will gracefully shut itself down. Shridhar I'm curious ... do MySQL lists talk about this as much as we do? What do they say? Well, we run Slashdot. Well, we can select count(*) faster We have all the features they do! Nobody uses views or triggers! Jeff ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Domains (was Re: [GENERAL] int1?)
Ron Johnson writes: test1=# create domain d_tinyint as smallint constraint chk_tinyint CHECK (smallint between 0 and 255); ERROR: DefineDomain: CHECK Constraints not supported So, how would I create a domain that limits a smallint? You would have to wait for PostgreSQL 7.4. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] int1?
On Thu, 9 Oct 2003, Sean Chittenden wrote: Is there any date type that can be used for 0-255 values? Like an int1 or byte column. A SMALLINT is two bytes on disk, use char instead. This is a hidden However char has some serious deficiencies IIRC, such as the fact that there's no int-char casts and it's standard I/O format is characters. You can use ascii and chr to get around some of that, but it's ugly. goodie in PostgreSQL and one that I wish was exposed via a more conventional syntax (*hint hint*). If we were going to do that I think we'd be better off making a new type and leaving char alone. ---(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: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Oliver Elphick wrote: But as far as Debian is concerned, paragraph 1 applies: 1. Free use for those who are 100% GPL If your application is licensed under GPL or compatible OSI license approved by MySQL AB, you are free and welcome to ship any GPL software of MySQL AB with your application. By application we mean any type of software application, system, tool or utility. For doing this, you do not need a separate signed agreement with MySQL AB, because the GPL text is sufficient... That makes it free under the Debian Free Software Guidelines, so I have no grounds for requesting its removal. :-( Just out of curiosity, what does Debian make MySQL's rather bizarre interpretaion of the GPL: http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#Copyright --- begin quote You need a commercial license: [...] When you distribute a non-|GPL| application that *only* works with the |MySQL| software and ship it with the |MySQL| software. This type of solution is considered to be linking even if it's done over a network. --- end quote Linking over a network? What stops some GPL'ed web server (or commercial one for that matter) from demanding non-free licensing for web clients that connect to it? - Marsh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] undefined reference to 'pg_detoast_datum'
Joe Conway wrote:
D. Stimits wrote:
A google search shows very little concerning the pg_detoast_datum
undefined reference link error. It is looking more like the V1 has to
be skipped and I'll have to go back to V0 if I can't get this to work.
You haven't shown us your function, so it's a bit difficult to help, but
in any case you do *not* want to use the V0 calling conventions -- they
are deprecated and subject to removal in the future. V1 certainly works
-- I've done many V1 C functions that accept text arguments. Take a look
at some of the contrib folder extensions if you need examples.
unless someone can magically make the default PostgreSQL version on RH
7.3 change to a newer version of PostgreSQL, then a newer version
can't be used]
Why not? I have Postgres 7.3.4 running on my RH 7.3 server. Here are RH
7.3 RPMs:
ftp://ftp8.us.postgresql.org/pub/pgsql/binary/v7.3.4/RPMS/redhat-7.3/
I'll consider this, but it greatly complicates things to require users
add the non-redhat version. I understand the benefits of doing so, but
convincing people to do this just to try out a package is not so easy.
Joe
Here is a simple echo function that is being used:
#include pgsql/server/postgres.h
#include string.h
#include pgsql/server/fmgr.h
PG_FUNCTION_INFO_V1(reverse_path);
Datum my_echo(PG_FUNCTION_ARGS)
{
text* arg = PG_GETARG_TEXT_P(0);
text* echo_arg = (text *)palloc(sizeof(arg));
memcpy(
(void *)(echo_arg),
(void *)(arg),
sizeof(arg)
);
PG_RETURN_TEXT_P(echo_arg);
}
Keep in mind that this isn't being run yet, it fails at link stage. As
another test, I have simplified the above just to test link time (not
intended to run, just as a test of link/compile):
#include pgsql/server/postgres.h
#include pgsql/server/fmgr.h
PG_FUNCTION_INFO_V1(reverse_path);
Datum my_echo(PG_FUNCTION_ARGS)
{
text* arg = PG_GETARG_TEXT_P(0);
PG_RETURN_TEXT_P(arg);
}
The first function complains at link time of missing link function
pg_detoast_datum, CurrentMemoryContext, and MemoryContextAlloc, while
the latter complains only of missing pg_detoast_datum.
In both cases, link libraries are:
-lkrb5 -lk5crypto -lcom_err -lpq -ldl
Now I am still scratching my head, wondering how it is that
pg_detoast_datum is a V0 function, and I can compile V0 libraries just
fine, but can't even compile a V1, which supposedly does not use
pg_detoast_data? The additional link failures when using palloc make me
feel there is some mysterious unnamed library that is missing when using
V1, aside from -lpq. When I go in to compile the src/test/regress/
directory of the source from the rpm used in Redhat 7.3, I see no signs
that anything else is required. Something simple must be missing under
the PostgreSQL 7.2 version. Is there a separate library that needs
linking under a V1 interface, in addition to those named above?
D. Stimits
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Postgres 7.4 : ECPG not Thread-safe
Hi,
This is the wrong mailing list but I don't sent it to pgsql-bugs with
success.
---BeginMessage---
POSTGRESQL BUG REPORT TEMPLATE
Your name : Thierry Missimilly
Your email address : [EMAIL PROTECTED]
System Configuration
-
Architecture (example: Intel Pentium) : Pentium IV 1.8 Ghz
Operating System (example: Linux 2.0.26 ELF) : Linux Red Hat 7.3
PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL-7.4Beta2
Beta4
Compiler used (example: gcc 2.95.2) : gcc 2.96
Please enter a FULL description of your problem:
I think i have a Thread-safe problem. PostgreSQL developpers will find a
test program to reproduce it on their lab.
I have, already sent this to pgsql-general and pgsql-hacker, but get
back not answers. But neve mind.
I've built Postgresql with the option --enable-thread-safety --with-tcl.
And now run a pgbench like program call pgbch.pgc (attached with this
mail).
The main differences of pgbch.pgc are :
1) Pro*c code
2) Multi-threaded
3) Some specific code for Oracle
4) Some specific dynamic trace for debug
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
--
To compile :
--
ecpg -o pgbhc.c pgbch.pgc
cc -g -D_THREAD_SAFE -D_REENTRANT -I /usr/local/pgsql/include -L
/usr/local/pgsql/lib -lpthread
-lecpg -o pgbch pgbch.c
To execute :
-
pgbch -i base_s1
pgbch -c3 -t10 base_s1
(you can add -d10 to have debug traces)
Well, the problem is that sometime pgbch hang. Run it 3 or 4 times and
it will hang.
I don't know if it is really link to Postgres and ECPG but if you
modifiy pgbch.pgc in the way that the
transaction is limited to BEGIN; END; , it will never hang.
pgbch with 3 clients means 4 processes, but when then hang occurs,
generally, one client has finished
its 10 transactions and there still are 3 processes.
A look at gdb (gdb pgbch pid) gives (See the attach file pgbch.gdb) :
1) The main thread is in pthread_join() waitting for the 2 other child
threads.
2) One thread has finished, but is hanged in pqSocketPoll()
3) The last other thread is waitting to start running in
__pthread_manager()
Well, i'm not an expert in Linux multi-thread, but it seems the thread
(2) is waitting from a message
from the postgresql backend which never comes and therefore blocks the
others threads.
I hope this will help to give back Postgres 7.4 more robust.
You will find attached the pgbch.pgc source and pgbch.gdb trace get on
Postgresql-7.4beta2
(they are the same with Postgresql-7.4Beta4)
Thierry Missimilly
/*
* $Header: /home/projects/pgsql/cvsroot/pgsql/contrib/pgbench/pgbench.c,v 1.8
2001/02/10 02:31:25 tgl Exp $
*
* pgbench: a simple TPC-B like benchmark program for PostgreSQL
* written by Tatsuo Ishii
*
* Copyright (c) 2000 Tatsuo Ishii
*
* Permission to use, copy, modify, and distribute this software and
* its documentation for any purpose and without fee is hereby
* granted, provided that the above copyright notice appear in all
* copies and that both that copyright notice and this permission
* notice appear in supporting documentation, and that the name of the
* author not be used in advertising or publicity pertaining to
* distribution of the software without specific, written prior
* permission. The author makes no representations about the
* suitability of this software for any purpose. It is provided as
* is without express or implied warranty.
VERSION 3.2 */
#include stdio.h
#include errno.h
#include stdlib.h
#include pthread.h
#include unistd.h
#ifdef WIN32
#include win32.h
#else
#include sys/time.h
#include unistd.h
#ifdef HAVE_GETOPT_H
#include getopt.h
#endif
#ifdef HAVE_SYS_SELECT_H
#include sys/select.h
#endif
/* for getrlimit */
#include sys/resource.h
#endif /* WIN32 */
/
* some configurable parameters */
exec sql include sqlca;
#ifdef ORACLE
#define EXIT_THREAD(a) return(NULL)
#else
#define EXIT_THREAD(a) pthread_exit(NULL) //return(NULL)
#endif
#define MAXCLIENTS 1024 /* max number of clients allowed */
#define a_result char
int nclients = 1;/* default number of simulated clients */
int ncli_connected ;/* Connected clients */
int nb_sql_failed ;/* nb SQL failed */
int num_cli = 0;
int nxacts = 10;/* default number of transactions per Clients */
/*
* scaling factor. for example, tps = 10 will make 100 tuples of
* accounts table.
*/
typedef struct
{
int finish;
char name[32];
} st_connection;
int tps = 1;
int gi_debug = 0;/* debug flag */
char *gdbName;
st_connection gtab_cnx[MAXCLIENTS];
#ifdef
Re: [GENERAL] Replication Bundled with Main Source.
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Joshua D. Drake wrote: It is not that we don't want to include replication in the base project it is that ERserver does not meet the requirements of what can be included in the base project. Specifically (I believe) the requirement of Java. Maybe they will move to C someday. Well, JDBC requires Java, and it's still in the main distro. I think the real answer is that until recently, ERserver wasn't open source and we didn't have the option to include it. Now that it is open source, we could think about it. Having looked at the code, I think it's definitely not ready for prime time, but it could get there with some work. When it's of comparable solidity to the base project I'd be in favor of adding it to the base distro. Unfortunately I don't think it'll get there ever. There is a fundamental design flaw in the system that is not fixable (there are multiple, but this is one of the biggies). That is that eRServer only remembers that a row has been modified, but not what, in what order, not even how often. The problem is really easy to demonstrate. With a UNIQUE constraint on a column, you change the values of two rows like A-C B-A C-B If these 3 changes fall into one snapshot, you have no chance to replicate that. eRServer tries to do A-B B-A and whatever order it tries, you'd need a deferred UNIQUE constraint to get it done, and I don't have the slightest clue how the ever get _that_ implemented. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] undefined reference to 'pg_detoast_datum'
D. Stimits wrote: A google search shows very little concerning the pg_detoast_datum undefined reference link error. It is looking more like the V1 has to be skipped and I'll have to go back to V0 if I can't get this to work. You haven't shown us your function, so it's a bit difficult to help, but in any case you do *not* want to use the V0 calling conventions -- they are deprecated and subject to removal in the future. V1 certainly works -- I've done many V1 C functions that accept text arguments. Take a look at some of the contrib folder extensions if you need examples. unless someone can magically make the default PostgreSQL version on RH 7.3 change to a newer version of PostgreSQL, then a newer version can't be used] Why not? I have Postgres 7.3.4 running on my RH 7.3 server. Here are RH 7.3 RPMs: ftp://ftp8.us.postgresql.org/pub/pgsql/binary/v7.3.4/RPMS/redhat-7.3/ Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Thu, 2003-10-09 at 08:33, [EMAIL PROTECTED] wrote: [snip] that a shutdown process that isn't tested can cause problems even with commercial databases. And as someone who has to put up with MySQL on Then that's a piss-poor commercial DBMS, since that means that the DB would be corrupt if the box crashed hard. -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA After listening to many White House, Pentagon CENTCOM briefings in both Gulf Wars, it is my firm belief that most senior correspondents either have serious agendas that don't get shaken by facts, or are dumb as dog feces. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
