Re: [GENERAL] SAS Raid10 vs SATA II Raid10 - many small reads and writes
Phillip Berry wrote: Hardware raid controller for both options, but I'm not sure what brand (yet). The reason you won't ever find a good general answer to this question is that it's so close that you need to know the exact controller cards and the disks used in each situation to have any hope of guessing which will work out better. Sometimes you get lucky and one case has a know poor performer, so you just get the other. Next tiebreak is management utilites, which count for a lot more than a small performance difference. If both are good on both of those, then maybe it's worth the trouble to model each possibility against your workload, with accurate numbers to substitute into any such guess. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replacing an index item
Hello everybody, I have a question about the replacement of an item in an index page. I want to overwrite an existing item inside an index page. However, I noticed that each time, I replaced an item, the free space in the given page was decreasing. I didn't want to delete the existing item and insert a new one properly, however this seems to be the only option I have (based on this observation). My code (in C) was the following before: // key tuple of child element iid = PageGetItemId(state-stack-parent-page, state-stack-parent-childoffnum); ItemIdSetUnused(iid); PageIndexTupleDelete(state-stack-parent-page, state-stack-parent-childoffnum); // key tuple for parent is first tuple of children entry keyTup = (IndexTuple) PageGetItem(state-stack-page, PageGetItemId(state-stack-page, FirstOffsetNumber)); PageAddItem(state-stack-parent-page, (Item) keyTup, IndexTupleSize(keyTup), state-stack-parent-childoffnum, true, false); However, this resulted (as I already mentioned) in an ever decreasing free space in the page which after that resulted in a page overflow at some point (resulting in splits etc). The code, I now use looks like the following: PageIndexTupleDelete(state-stack-parent-page, state-stack-parent-childoffnum); //PageIndexTupleDelete(state-stack-parent-page, state-stack-parent-childoffnum); // key tuple for parent is first tuple of children entry keyTup = (IndexTuple) PageGetItem(state-stack-page, PageGetItemId(state-stack-page, FirstOffsetNumber)); itupvec[0] = (IndexTuple) palloc0(IndexTupleSize(keyTup)); memcpy(itupvec[0], keyTup, IndexTupleSize(keyTup)); putTuple(state-r, state-stack-parent-page, state-stack-parent-buffer, itupvec, 1, state-stack-parent-childoffnum, btreestate); Whereas the putTuple refers to a function that puts the tuple and shifts existing tuples, if necessary. However, I want to avoid this, because it still requires a little bit of overhead to reorganize it properly. I would prefer the first option, but it does not seem to have good results based on the waste of space. Could anyone please give me some help according to this issue? Best regards Carsten Kropf
[GENERAL] how to remove super user
I uninstall an older version and tried installing 8,4 but now it keeps asking for a password from Superuser account... Any ideas how to remove this? Thanks for reading. _ Windows 7: helpt je meer voor elkaar te krijgen. http://windows.microsoft.com/nl-BE/windows7/products/home?os=win7
Re: [GENERAL] how to remove super user
what operating system? did you try to remove postgres system account before installing new version? 2010/3/11 Jowad Bouzian jowadbouzi...@hotmail.com: I uninstall an older version and tried installing 8,4 but now it keeps asking for a password from Superuser account... Any ideas how to remove this? Thanks for reading. BE-langrijk nieuws! Nu ook @hotmail.BE-adressen in België. Klik en creëer -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to remove super user
Hi, How are you trying to install it? (Using one click installer...?) And which version of postgresql did you have earlier? -- Ashesh Vashi On Thu, Mar 11, 2010 at 1:01 PM, Jowad Bouzian jowadbouzi...@hotmail.comwrote: I uninstall an older version and tried installing 8,4 but now it keeps asking for a password from Superuser account... Any ideas how to remove this? Thanks for reading. -- BE-langrijk nieuws! Nu ook @hotmail.BE-adressen in België. Klik en creëerhttp://85.255.193.198/specials/hotmailbe/nl/
Re: [GENERAL] Naming conventions for lots of stored procedures
On 3/10/2010 11:52 PM, Chris Travers wrote: There are two major limitations here of schemas: 1) They can't be nested leading again to possible namespace ambiguity. 2) there are a number of requests to try to get the application to install into an arbitrary, nonpublic schema. If schemas could be nested this would solve both of these problems. However, if the above is anywhere near a complete list of schemas for 1200 procedures, you must also have some strong naming conventions to prevent collisions. I would be interested in what they are. Best wishes, Chris Travers This is an app i took over and there was no strong name convention plus an godly amount of overloaded procedures. the procedures use very very long names example createardebitmemo(int, text, text date, numeric, text, int, int mint date, int int, numeric ) createarcreditmemo(integer, text, text, date, numeric, text, integer, integer, integer, date, integer, integer, numeric, integer, integer) this means Create Accounts Receiver Debit Memo deleteaccount(integer) deleteaccountingperiod(integer) deleteaccountingyearperiod(integer) deletecustomer(integer) after the moving the functions into schemas this is how one would/could call them. gl.deleteaccount(integer) gl.deleteaccountingperiod(integer) gl.deleteaccountingyearperiod(integer) ar.deletecustomer(integer) ar.createardebitmemo(int, text, text date, numeric, text, int, int mint date, int int, numeric ) ar.createardreditmemo(integer, text, text, date, numeric, text, integer, integer, integer, date, integer, integer, numeric, integer, integer) Now one problem is if 2 functions have the same name, same number and type of inputs then Postgresql will throw ambiguous error, if the search path includes the 2 schemas where the functions are stored . I wonder if any database out there allows for nesting schemas. Which i'm at a loss why nesting would help solve any problem what so ever. I imagine the search path on some connections would be all inclusive so ambiguous names is not solved. Also would not be a big fan typing something like AR.Customer.Editing.Delete(ID) what has been gained??? think if the search path was all inclusive AR.Contact.Editing.Delete WIP.WorkOrder.Delete and this was called Select Delete(5784); Postgresql will through ambiguous error which delete, the one in AR.Customer, AR.Contact or WIP.Workorder schema. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] instaling ossp-uuid
I just downloaded and compiled UUID on CENTOS 5.4 x86, I also downloaded, compiled and installed ossp-uuid and to install it i execute this as postgres user: psql -d test_database -U postgres -f /usr/share/pgsql/uuid.sql SET SET psql:/usr/share/pgsql/uuid.sql:37: ERROR: function uuid_in(cstring) does not exist psql:/usr/share/pgsql/uuid.sql:38: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:39: ERROR: function uuid_recv(internal) does not exist psql:/usr/share/pgsql/uuid.sql:40: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:41: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:42: ERROR: target data type uuid does not exist psql:/usr/share/pgsql/uuid.sql:43: ERROR: source data type uuid does not exist psql:/usr/share/pgsql/uuid.sql:45: ERROR: function uuid(cstring) does not exist psql:/usr/share/pgsql/uuid.sql:46: ERROR: function uuid(integer) does not exist psql:/usr/share/pgsql/uuid.sql:47: ERROR: function uuid(integer, cstring, cstring) does not exist psql:/usr/share/pgsql/uuid.sql:49: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:50: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:51: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:52: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:53: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:54: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:55: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:56: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:57: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:58: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:59: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:60: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:62: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:63: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:64: ERROR: operator class uuid_ops does not exist for access method hash psql:/usr/share/pgsql/uuid.sql:65: ERROR: operator class uuid_ops does not exist for access method btree BEGIN psql:/usr/share/pgsql/uuid.sql:76: ERROR: could not load library /usr/lib/pgsql/uuid.so: libuuid.so.16: cannot open shared object file: No such file or directory psql:/usr/share/pgsql/uuid.sql:81: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:86: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:91: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:101: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:104: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:119: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:124: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:129: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:138: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:143: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:148: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:153: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:158: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:163: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:170: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:177: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:185: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:193: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:201: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:209: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:/usr/share/pgsql/uuid.sql:218: ERROR: current transaction is aborted, commands
Re: [GENERAL] instaling ossp-uuid
On Thu, 2010-03-11 at 13:54 +0100, dunas...@dunasoft.es wrote: psql:/usr/share/pgsql/uuid.sql:37: ERROR: function uuid_in(cstring) does not exist psql:/usr/share/pgsql/uuid.sql:38: ERROR: type uuid does not exist psql:/usr/share/pgsql/uuid.sql:39: ERROR: function uuid_recv(internal) does not exist ... snip Could you please try installing these uuid RPMs and test against these? http://yum.pgrpms.org/8.3/redhat/rhel-5-x86_64/repoview/letter_u.group.html (Replace 8.3 with 8.4 if you are using 8.4...) I have tested it before, and it worked. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Replacing an index item
Carsten Kropf ckro...@fh-hof.de writes: I have a question about the replacement of an item in an index page. I want to overwrite an existing item inside an index page. Why exactly do you want to do that? How are you going to make it transactionally correct or crash-safe? FWIW, I think the ItemIdSetUnused call is wrong or at least unnecessary. Perhaps it is confusing PageIndexTupleDelete. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
On 3/10/2010 11:52 PM, Chris Travers wrote:
There are two major limitations here of schemas:
1) They can't be nested leading again to possible namespace ambiguity.
2) there are a number of requests to try to get the application to
install into an arbitrary, nonpublic schema.
If schemas could be nested this would solve both of these problems.
However, if the above is anywhere near a complete list of schemas for
1200 procedures, you must also have some strong naming conventions to
prevent collisions. I would be interested in what they are.
Best wishes,
Chris Travers
This is an app i took over and there was no strong name convention plus
an godly amount of overloaded procedures.
In the current framework we can't handle overloaded functions. The
program is written in Perl (with no strong typing). The next version
will probably allow a limited amount of overloading.
the procedures use very very long names example
createardebitmemo(int, text, text date, numeric, text, int, int mint
date, int int, numeric )
createarcreditmemo(integer, text, text, date, numeric, text, integer,
integer, integer, date, integer, integer, numeric, integer, integer)
We'd probably add underscores... Maybe putting the package last would
be better than putting it first.
this means Create Accounts Receiver Debit Memo
deleteaccount(integer)
deleteaccountingperiod(integer)
deleteaccountingyearperiod(integer)
deletecustomer(integer)
after the moving the functions into schemas this is how one would/could
call them.
gl.deleteaccount(integer)
gl.deleteaccountingperiod(integer)
gl.deleteaccountingyearperiod(integer)
ar.deletecustomer(integer)
ar.createardebitmemo(int, text, text date, numeric, text, int, int mint
date, int int, numeric )
ar.createardreditmemo(integer, text, text, date, numeric, text, integer,
integer, integer, date, integer, integer, numeric, integer, integer)
Sure. This can be handled by our stored procedure mapping API.
Now one problem is if 2 functions have the same name, same number and
type of inputs then Postgresql will throw ambiguous error, if the
search path includes the 2 schemas where the functions are stored .
We use fully qualified function names in our calls. Currently the
schema is admin-definable. If it were to be set per module, that
would be possible too.
I wonder if any database out there allows for nesting schemas.
Oracle allows nested packages which provides some similar functionality.
The manual recommends using schemas instead of packages when porting
from Oracle. So at least some RDBMS's provide some sort of nested
logical grouping to functions.
Which
i'm at a loss why nesting would help solve any problem what so ever. I
imagine the search path on some connections would be all inclusive so
ambiguous names is not solved. Also would not be a big fan typing
something like
AR.Customer.Editing.Delete(ID)
Well, the way we would use something like this would be (Perl pseudocode here):
our const $nspname = 'invoice.ar'
sub save_invoice {
my ($self) = @_;
$self-exec_mapped_proc({ procname = 'save'});
}
sub approve_invoice {
my ($self) = @_;
$self-exec_mapped_proc({ procname = 'approve'});
}
exec_mapped_proc then resolves the procname to its fully qualified
name (invoice.ar.save, invoice.ar.approve), discovers named arguments,
maps them in, and calls it.
what has been gained???
think if the search path was all inclusive
AR.Contact.Editing.Delete
WIP.WorkOrder.Delete
and this was called
Select Delete(5784);
Postgresql will through ambiguous error which delete, the one in
AR.Customer, AR.Contact or WIP.Workorder schema.
The way I look at it, boring stuff can be automated. We intend to
provide reference implementations for how this mapping works anyway so
that addons can be written perhaps in other languages.
Best Wishes,
Chris Travers
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] log_statement and syslog severity
Stuart Bishop wrote: It might be possible to trick csvlog to log to a static filename, and perhaps substituting that with a named pipe might work (under unix at least). As someone who did a bit of the work on the CSV log feature, I'll tell you the way you have to note the log filename, account for rotations, and everything else involved makes for a painful API to actually use was obvious from day one. What I suggested was that many admins would want a tail-f like API available to grabs at they come out, without having to care about the underlying name. But no one has dumped enough development resources into actually building one of them. At the time, there were a host of genuine bugs in the logging approached used for CVS logs, and just closing them all up before release time was difficult enough. And there hasn't been enough asking about it to inspire development since. I need to be analyzing log messages from PostgreSQL in real time, so am starting to investigate solutions. It seems painful, which would be avoidable for future generations if PostgreSQL could spawn a subprocess and send log messages to that in a machine readable format. That is the only direction something like this is going to get built in. What Bruce was suggesting is that the idea of building any more logging intelligence into the database itself will never go anywhere. The alternate question of how do I get a better API for exporting real-time logging messages I can process? is still quite open in my mind. The idea Magnus was already suggesting here, to add an alternate pipe destination, would be one useful step forward here. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
On Thu, Mar 11, 2010 at 08:38:46AM -0800, Chris Travers wrote: On 3/10/2010 11:52 PM, Chris Travers wrote: Which i'm at a loss why nesting would help solve any problem what so ever. I imagine the search path on some connections would be all inclusive so ambiguous names is not solved. Also would not be a big fan typing something like AR.Customer.Editing.Delete(ID) Why dont you create such a function if you need it? CREATE FUNCTION AR.Customer.Editing.Delete(integer) ... Regards, Gerhard signature.asc Description: Digital signature
[GENERAL] Small install (w/ pSQLODBC support) needed.
I have written a binary wiki engine using PostgreSQL and FPC - it is sweet. But, now I have a customer and I am here today to install the product (along with other tasks) and their Linux box does not run X. My steps are down perfect, run install from EnteriseDB and drop in my product and .conf file. I am not sure how to do this from a shell, will the installer from EnterpriseDB work from the shell and still install the ODBC and server? // I use unixODBC (ODBC in general) to support any back-end for my Wiki. But, being an old Ora-DBA, I LOVE POSTGRESQL! Thanks for any guidance you guys can give me! Ozz Thank you, G.E. Ozz Nixon CEO/Sr. Software Architect 3F, LLC 125 Robin Dr. Barto PA 19504 Office: 1-484-363-2304 Cell: 1-610-698-7976 Email: o...@3flabs.com The information contained in this communication is confidential. It is intended only for the use of the recipients named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are here by notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend the communication to the sender and delete the original message or any copy of it from your computer systems. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Joining one-to-one and one-to-many tables
I'm stumped about the best was to retrieve the most recent entry in a
one-to-many type of table and combine it with a more standard query
that joins one-to-one.
I have defined these:
jr...@wykids localhost# \d trainer_dates
Table public.trainer_dates
Column | Type | Modifiers
+-+-
tr_date_id | integer | not null default
nextval('trainer_dates_tr_date_id_seq'::regclass)
tr_date_short_name | text| not null
tr_date_name | text| not null
tr_date_active | boolean | default false
Indexes:
trainer_dates_pkey PRIMARY KEY, btree (tr_date_id)
Referenced by:
TABLE trainers_trainer_dates CONSTRAINT
trainers_trainer_dates_trs_tr_date_id_fkey FOREIGN KEY (trs_tr_date_id)
REFERENCES trainer_dates(tr_date_id)
and
jr...@wykids localhost# \d trainers_trainer_dates
Table public.trainers_trainer_dates
Column|Type | Modifiers
--+-+--
trs_tr_date_pp_id| integer |
trs_tr_date_tr_id| integer |
trs_tr_date_id | integer |
trs_tr_date | date| default
('now'::text)::date
trs_tr_date_recorded | timestamp without time zone | default now()
Foreign-key constraints:
trainers_trainer_dates_trs_tr_date_id_fkey FOREIGN KEY (trs_tr_date_id)
REFERENCES trainer_dates(tr_date_id)
trainers_trainer_dates_trs_tr_date_pp_id_fkey FOREIGN KEY
(trs_tr_date_pp_id) REFERENCES people(pp_id)
trainers_trainer_dates_trs_tr_date_tr_id_fkey FOREIGN KEY
(trs_tr_date_tr_id) REFERENCES trainers(tr_id)
Here are the records in trainer_dates:
jr...@wykids localhost# select * from trainer_dates;
tr_date_id | tr_date_short_name |tr_date_name| tr_date_active
+++
1 | tr_active_date | Active | t
2 | tr_inactive_date | Inactive | t
3 | tr_destroyed_date | Destroyed | t
4 | tr_pending_date| Pending| t
5 | tr_waiting_for_update_date | Waiting for Update | t
6 | tr_last_updated_date | Last Updated Date | t
7 | tr_application_date| Application Date | t
8 | tr_denied_date | Denied | f
9 | tr_approved_date | Approved | f
(9 rows)
Here is a sample of the data that might be in trainers_trainer_dates:
jr...@wykids localhost# select trs_tr_date, trs_tr_date_recorded, tr_date_name
from trainers_trainer_dates join trainer_dates on trs_tr_date_id = tr_date_id
where trs_tr_date_tr_id = 1099 order by trs_tr_date_recorded desc;
trs_tr_date |trs_tr_date_recorded| tr_date_name
-++---
2010-03-11 | 2010-03-11 09:49:42.736914 | Pending
2009-12-23 | 2009-12-23 01:00:00| Inactive
2009-12-23 | 2009-12-23 00:00:00| Last Updated Date
2002-03-21 | 2002-03-21 00:00:00| Application Date
(4 rows)
(Most of the older trs_tr_date_recorded will not have a full timestamp--this
is a part of a table restructuring and the old table just kept a date. As I
move data into the new tables I add one hour to the timestamp of the current
status to make sure everything sorts correctly.)
The problem comes when I try to offer results to a query that says something
like show me all trainers whose current status is Pending. This query has
to join three tables, a people table with demographics, a trainers table that
holds a trainer id and some notes and the trainers_trainer_dates table. The
people and trainers table have a one-to-one correlation, while the
trainers_trainer_dates has a one-to-many relationship.
This query, for example, yields 2 results for the trainer referenced above,
instead of just his Pending record:
SELECT
pp_id,
pp_trainer_id,
name,
tr_status,
max(trs_tr_date_recorded)
FROM
(
SELECT
pp_id,
pp_trainer_id,
pp_last_name || ', ' || pp_first_name as name,
trs_tr_date_id,
tr_date_name as tr_status,
trs_tr_date_recorded
FROM people
JOIN trainers_trainer_dates on pp_id = trs_tr_date_pp_id
JOIN trainer_dates on tr_date_id = trs_tr_date_id WHERE
trs_tr_date_id NOT IN (
SELECT tr_date_id from trainer_dates WHERE
tr_date_name in ('Last Updated Date','Application Date')
)
GROUP BY pp_id, pp_trainer_id, pp_last_name, pp_first_name,
trs_tr_date_id, tr_date_name, trs_tr_date_recorded
) as foo2
WHERE pp_id in (
SELECT pp_id from people WHERE
pp_trainer_id IS NOT NULL and pp_provisional_p = 'f'
INTERSECT
Re: [GENERAL] Small install (w/ pSQLODBC support) needed.
Yes the EnterpriseDB installer works from the shell, try running the installer with ' --mode text ' or --help for all options available. On 3/11/10 11:17 PM, Ozz Nixon wrote: I have written a binary wiki engine using PostgreSQL and FPC - it is sweet. But, now I have a customer and I am here today to install the product (along with other tasks) and their Linux box does not run X. My steps are down perfect, run install from EnteriseDB and drop in my product and .conf file. I am not sure how to do this from a shell, will the installer from EnterpriseDB work from the shell and still install the ODBC and server? // I use unixODBC (ODBC in general) to support any back-end for my Wiki. But, being an old Ora-DBA, I LOVE POSTGRESQL! Thanks for any guidance you guys can give me! Ozz Thank you, G.E. Ozz Nixon CEO/Sr. Software Architect 3F, LLC 125 Robin Dr. Barto PA 19504 Office: 1-484-363-2304 Cell: 1-610-698-7976 Email: o...@3flabs.com The information contained in this communication is confidential. It is intended only for the use of the recipients named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are here by notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend the communication to the sender and delete the original message or any copy of it from your computer systems. -- Regards, Sachin Srivastava EnterpriseDB http://www.enterprisedb.com, the Enterprise Postgres http://www.enterprisedb.com company.
Re: [GENERAL] kernel version impact on PostgreSQL performance
Greg Smith a écrit : Rodger Donaldson wrote: Cyril Scetbon wrote: Does anyone know what can be the differences between linux kernels 2.6.29 and 2.6.30 that can cause this big difference (TPS x 7 !) http://www.phoronix.com/scan.php?page=articleitem=linux_2624_2633num=2 http://www.csamuel.org/2009/04/11/default-ext3-mode-changing-in-2630 Yeah, I realized I answered the wrong question--Cyril wanted to know why was 2.6.30 so much faster?, not why did 2.6.33 get so much slower?, which is what I was focusing on. There's a good intro to what happened to speed up 2.6.30 at http://lwn.net/Articles/328363/ , with the short version being the kernel stopped caring about data integrity at all in 2.6.30 by switching to writeback as its default. The give you an idea how wacky this is, less than a year ago Linus himself was ranting about how terrible that specific implementation was: http://lkml.org/lkml/2009/3/24/415 http://lkml.org/lkml/2009/3/24/460 and making it the default exposes a regression to bad behavior to everyone who upgrades to a newer kernel. I'm just patiently waiting for Chris Mason (who works for Oracle--they care about doing the right thing here too) to replace Ted Tso as the person driving filesystem development in Linux land. That his data=guarded implementation was only partially merged into 2.6.30, and instead combined with this awful default change, speaks volumes about how far the Linux development priorities are out of sync (pun intended) with what database users expect. See http://www.h-online.com/open/news/item/Kernel-Log-What-s-coming-in-2-6-30-File-systems-New-and-revamped-file-systems-741319.html for a summary on how that drama played out. I let out a howling laugh when reading this was because The rest have been put on hold, with the development cycle already entering the stabilisation phase. Linux kernel development hasn't had a stabilization phase in years. It's interesting that we have pgbench available as a lens to watch all this through, because in its TPC-B-like default mode it has an interesting property: if performance on regular hardware gets too fast, it means data integrity must be broken, because regular drives can't do physical commits very often. What Phoronix should be doing is testing simple fsync rate using something like sysbench first[1], and if those numbers come back higher than disk RPM rate declare the combination unusable for PostgreSQL purposes rather than reporting on the fake numbers. [1] http://www.westnet.com/~gsmith/content/postgresql/pg-benchmarking.pdf , page 26 Thank you Greg, that was exactly the answer I was waiting for. Everyone should take care about the changes made when such surprising numbers are provided ! Regards -- Cyril SCETBON -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
2010/3/11 Gerhard Heift ml-postgresql-20081012-3...@gheift.de: On Thu, Mar 11, 2010 at 08:38:46AM -0800, Chris Travers wrote: On 3/10/2010 11:52 PM, Chris Travers wrote: Which i'm at a loss why nesting would help solve any problem what so ever. I imagine the search path on some connections would be all inclusive so ambiguous names is not solved. Also would not be a big fan typing something like AR.Customer.Editing.Delete(ID) Why dont you create such a function if you need it? CREATE FUNCTION AR.Customer.Editing.Delete(integer) ... it's not good idea. Case sensitive names are usually problem. Customer.Editing.Delete isn't best identifier too - Editing is useless. customer_delete is enough. Regards Pavel Stehule Regards, Gerhard -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) iEYEARECAAYFAkuZH/sACgkQa8fhU24j2fml2gCgkpZfQ53fxotGDBoG4BYgIUZG 2vUAn19yVUFq6hzAHFN0hAONiydtqq3B =ZLVm -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Joining one-to-one and one-to-many tables
Jeff:
I may not fully understand the situation or the data you're trying to retrieve,
but if you're trying to get show me all trainers whose current status is
Pending, I would go backwards to how you're doing it. If you're only
concerned about those records with tr_date_name = Pending, then you only need
to query for those specific records and join to trainers_trainer_dates.
SELECT
pp_id,
pp_trainer_id,
name,
tr_status,
trs_tr_date_recorded
FROM
trainer_dates
JOIN trainers_trainer_dates ON tr_date_id = trs_tr_date_id
JOIN people ON trs_tr_date_pp_id = pp_id
WHERE
tr_status='Pending'
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jeff Ross
Sent: Thursday, March 11, 2010 11:37 AM
To: PostgreSQL
Subject: [GENERAL] Joining one-to-one and one-to-many tables
I'm stumped about the best was to retrieve the most recent entry in a
one-to-many type of table and combine it with a more standard query
that joins one-to-one.
I have defined these:
jr...@wykids localhost# \d trainer_dates
Table public.trainer_dates
Column | Type | Modifiers
+-+-
tr_date_id | integer | not null default
nextval('trainer_dates_tr_date_id_seq'::regclass)
tr_date_short_name | text| not null
tr_date_name | text| not null
tr_date_active | boolean | default false
Indexes:
trainer_dates_pkey PRIMARY KEY, btree (tr_date_id)
Referenced by:
TABLE trainers_trainer_dates CONSTRAINT
trainers_trainer_dates_trs_tr_date_id_fkey FOREIGN KEY (trs_tr_date_id)
REFERENCES trainer_dates(tr_date_id)
and
jr...@wykids localhost# \d trainers_trainer_dates
Table public.trainers_trainer_dates
Column|Type | Modifiers
--+-+--
trs_tr_date_pp_id| integer |
trs_tr_date_tr_id| integer |
trs_tr_date_id | integer |
trs_tr_date | date| default
('now'::text)::date
trs_tr_date_recorded | timestamp without time zone | default now()
Foreign-key constraints:
trainers_trainer_dates_trs_tr_date_id_fkey FOREIGN KEY (trs_tr_date_id)
REFERENCES trainer_dates(tr_date_id)
trainers_trainer_dates_trs_tr_date_pp_id_fkey FOREIGN KEY
(trs_tr_date_pp_id) REFERENCES people(pp_id)
trainers_trainer_dates_trs_tr_date_tr_id_fkey FOREIGN KEY
(trs_tr_date_tr_id) REFERENCES trainers(tr_id)
Here are the records in trainer_dates:
jr...@wykids localhost# select * from trainer_dates;
tr_date_id | tr_date_short_name |tr_date_name| tr_date_active
+++
1 | tr_active_date | Active | t
2 | tr_inactive_date | Inactive | t
3 | tr_destroyed_date | Destroyed | t
4 | tr_pending_date| Pending| t
5 | tr_waiting_for_update_date | Waiting for Update | t
6 | tr_last_updated_date | Last Updated Date | t
7 | tr_application_date| Application Date | t
8 | tr_denied_date | Denied | f
9 | tr_approved_date | Approved | f
(9 rows)
Here is a sample of the data that might be in trainers_trainer_dates:
jr...@wykids localhost# select trs_tr_date, trs_tr_date_recorded, tr_date_name
from trainers_trainer_dates join trainer_dates on trs_tr_date_id = tr_date_id
where trs_tr_date_tr_id = 1099 order by trs_tr_date_recorded desc;
trs_tr_date |trs_tr_date_recorded| tr_date_name
-++---
2010-03-11 | 2010-03-11 09:49:42.736914 | Pending
2009-12-23 | 2009-12-23 01:00:00| Inactive
2009-12-23 | 2009-12-23 00:00:00| Last Updated Date
2002-03-21 | 2002-03-21 00:00:00| Application Date
(4 rows)
(Most of the older trs_tr_date_recorded will not have a full timestamp--this
is a part of a table restructuring and the old table just kept a date. As I
move data into the new tables I add one hour to the timestamp of the current
status to make sure everything sorts correctly.)
The problem comes when I try to offer results to a query that says something
like show me all trainers whose current status is Pending. This query has
to join three tables, a people table with demographics, a trainers table that
holds a trainer id and some notes and the trainers_trainer_dates table. The
people and trainers table have a one-to-one correlation, while the
trainers_trainer_dates has a one-to-many
[GENERAL] createdb: ... duplicate key value violates unique constraint pg_database_datname_index
Hi all, On a new x86_64 windows 7 SMP, a new database server was being set up by some test automation. We are using official 8.4.2 binaries. It runs: initdb -D c:\... --no-locale ... eventually this outputs the Success. You can now start the database server using ... message. Once that exits (with 0) it proceeds to start postgres: postgres -D c:\... Then we wait for the .pid file to show up. Once that happens we do createdb: createdb -h 127.0.0.1 -p 1234 abc createdb: database creation failed: ERROR: duplicate key value violates unique constraint pg_database_datname_index This is not reproducible and only happens occasionally. We do sometimes get the database is starting up error (and we just retry if that happens). For technical reasons I no longer remember, we found it problematic to use pg_ctl -w on windows, and I do not remember whether it always waited long enough anyway. Is there a proper way to wait for postgres to start up other than waiting until the pid file exists and retrying things whenever you get the database is starting up error? Is the error I am describing indicative of something else? Once, we got this slightly different error when creating the second table in the freshly created database: duplicate key value violates unique constraint pg_type_typname_nsp_index It had some primitive columns and a foreign key reference to the first table (which only had primitive columns). We run this test automation quite a few platforms and have never encountered this problem on any of them: - All flavors of windows NT from win2k onwards, including an older win7 box - linux: x86 + x86_64 - macos: x86_64 - solaris: sparc + x86 + x86_64 - Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: createdb: ... duplicate key value violates unique constraint pg_database_datname_index
Hi again, I'm going to answer my own question. I think there is a race where two database servers are being setup on the same port at the same time and two different tests end up trying to create the same DB on the same server at the same time. Let me know if you think I'm wrong :). Dave Vitek wrote: Hi all, On a new x86_64 windows 7 SMP, a new database server was being set up by some test automation. We are using official 8.4.2 binaries. It runs: initdb -D c:\... --no-locale ... eventually this outputs the Success. You can now start the database server using ... message. Once that exits (with 0) it proceeds to start postgres: postgres -D c:\... Then we wait for the .pid file to show up. Once that happens we do createdb: createdb -h 127.0.0.1 -p 1234 abc createdb: database creation failed: ERROR: duplicate key value violates unique constraint pg_database_datname_index This is not reproducible and only happens occasionally. We do sometimes get the database is starting up error (and we just retry if that happens). For technical reasons I no longer remember, we found it problematic to use pg_ctl -w on windows, and I do not remember whether it always waited long enough anyway. Is there a proper way to wait for postgres to start up other than waiting until the pid file exists and retrying things whenever you get the database is starting up error? Is the error I am describing indicative of something else? Once, we got this slightly different error when creating the second table in the freshly created database: duplicate key value violates unique constraint pg_type_typname_nsp_index It had some primitive columns and a foreign key reference to the first table (which only had primitive columns). We run this test automation quite a few platforms and have never encountered this problem on any of them: - All flavors of windows NT from win2k onwards, including an older win7 box - linux: x86 + x86_64 - macos: x86_64 - solaris: sparc + x86 + x86_64 - Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgEast Hotel discount deadline today!
03/11/2010 The hotel discount deadline is today, 03/11/2010. If you have not purchased your rooms by the end of today, you will pay at least 60.00 more per night. The hotel is also reaching capacity. It is doubly worth your attention to register today if you are seeking to do so. If you do not, you will not only pay more for your rooms, you may have to seek alternative lodging to enjoy PostgreSQL Conference East! For Hotel Information: http://www.postgresqlconference.org/east/2010/accommodations To register for the conference: https://www.postgresql.us/purchase Look forward to seeing you there and once again thank you to our Premium and Gold sponsors: Command Prompt, Inc: http://www.commandprompt.com/ EnterpriseDB: http://www.enterprisedb.com/ OmniTI: http://www.omniti.com/ OTG: http://www.otg-nc.com/ Red Hat: http://www.redhat.com/ -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] recuperar nodo en estado 3
tengo un problema ya habia replicado los datos en las BD 2010-03-11 19:23:58 DEBUG: pid 8252: starting health checking 2010-03-11 19:23:58 DEBUG: pid 8252: health_check: 0 th DB node status: 1 2010-03-11 19:23:58 DEBUG: pid 8252: health_check: 1 th DB node status: 3 2010-03-11 19:24:28 DEBUG: pid 8252: starting health checking 2010-03-11 19:24:28 DEBUG: pid 8252: health_check: 0 th DB node status: 1 2010-03-11 19:24:28 DEBUG: pid 8252: health_check: 1 th DB node status: 3 y me marca eso lo que hice fue incorporar un nodo en el pg.conf con los mismos datos que el nodo caido para que me quedara asi backend_hostname0 = 'pgsql1' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/data' backend_hostname1 = 'pgsql2' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/data' backend_hostname2 = 'pgsql2' backend_port2 = 5432 backend_weight2 = 1 backend_data_directory2 = '/var/lib/pgsql/data' y ahora que me conecto me queda asi 2010-03-11 19:27:56 DEBUG: pid 8384: starting health checking 2010-03-11 19:27:56 DEBUG: pid 8384: health_check: 0 th DB node status: 1 2010-03-11 19:27:56 DEBUG: pid 8384: health_check: 1 th DB node status: 3 2010-03-11 19:27:56 DEBUG: pid 8384: health_check: 2 th DB node status: 1 por que el nodo 1 nunca se recupero a pesar de que son los mismos gracias!!
Re: [GENERAL] dst question
Hi Tom, thanks for the response. Tom Lane wrote: jgirvin james.gir...@oasissystems.com.au writes: Australia will come out of DST on the 4th April 2010 at 03:00:00 and will be +9:30 from utc, currently we are +10:30 utc. OK, so this is a fall back transition for you guys, right? Yes this is correct Adelaide, South Aust will be coming out of (Australian) CDT back into (Australian) CST. This is 4 seconds into the last hour prior to dst changeover, now the to_timestamp result is showing +09:30 as the offset and now() correctly shows +10:30 # select now(), to_timestamp( to_char(now(),'-mm-dd hh24:mi:ss'),'-mm-dd hh24:mi:ss'); now| to_timestamp --+--- 2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30 (1 row) Can someone explain as to why the output from the to_timestamp shows the offset at +09:30 when within the hour of the dst changeover and is this expected The problem is that the output of to_char() is ambiguous, since you didn't include the timezone in the format spec. Times between 02:00 and 03:00 occur twice on that date, and there's no way to know which time 02:00:04 refers to. The assumption that to_timestamp uses (along with our other datetime input code) is that an ambiguous time should be resolved as standard time, ie, the second occurrence of 02:00:04. Okay, that makes sense. regards, tom lane __ Information from ESET NOD32 Antivirus, version of virus signature database 4933 (20100310) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- James Girvin Oasis Systems Pty Limited Level 1, 206 Greenhill Road Eastwood, South Australia 5063 Voice: +61 8 8372 9057 Fax: +61 8 8372 9051 http://www.oasissystems.com.au mailto:james.gir...@oasissystems.com.au This email is subject to the following policy: http://www.oasissystems.com.au/email_policy.html __ Information from ESET NOD32 Antivirus, version of virus signature database 4937 (20100311) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] querying the value of the previous row
I'm to write a query like:- select case when column_name1 value_of_previous(column_name1) then column_name1 end as column ,column_name2 from table ordered by column_name1, column_name2 in order to get:- column| column_name2 --+-- value_1_c1| value_1_c2 | value_2_c2 | value_3_c2 value_2_c1| value_4_c2 | value_5_c2 | value_6_c2 value_3_c1| value_7_c2 | value_8_c2 | value_9_c2 How do I do this? (I'm using pg 7.4) Chris -- Chris Velevitch Manager - Adobe Platform Users Group, Sydney m: 0415 469 095 www.apugs.org.au Adobe Platform Users Group, Sydney March 2010: ColdFusion Application Architecture for the Impatient and Using jQuery when Flash is Overkill Date: 29nd Mar 6pm for 6:30 start Details and RVSP on http://groups.adobe.com/posts/148c9056a4
Re: [GENERAL] querying the value of the previous row
In response to Chris Velevitch : I'm to write a query like:- select case when column_name1 value_of_previous(column_name1) then column_name1 end as column ,column_name2 from table ordered by column_name1, column_name2 Okay, with this table: test=# select * from foo; col1 | col2 --+-- 1 |1 1 |2 1 |3 2 |4 2 |5 2 |6 2 |7 3 |8 4 |9 5 | 10 (10 rows) you can do: test=# select case when col1::text coalesce(lag::text,'NULL') then col1 else null end as col1, col2 from ( select col1, lag(col1) over (range unbounded preceding ), col2 from foo order by col2 ) foo order by col2; col1 | col2 --+-- 1 |1 |2 |3 2 |4 |5 |6 |7 3 |8 4 |9 5 | 10 (10 rows) How do I do this? (I'm using pg 7.4) Unfortunately (for you), i'm using a window-function, in this case lag(), new since 8.4. Your version 7.4 has reached End-of-Lifetime, so i suggest you update to 8.4. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] querying the value of the previous row
Chris Velevitch wrote: I'm to write a query like:- select case when column_name1 value_of_previous(column_name1) then column_name1 end as column ,column_name2 from table ordered by column_name1, column_name2 in order to get:- column| column_name2 --+-- value_1_c1| value_1_c2 | value_2_c2 | value_3_c2 value_2_c1| value_4_c2 | value_5_c2 | value_6_c2 value_3_c1| value_7_c2 | value_8_c2 | value_9_c2 How do I do this? (I'm using pg 7.4) what does 'previous' mean here? thats not a concept SQL really has. anyways, that sort of report output is something you usually do via your reporting code -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
