PostgreSQL Database Forum

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...

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...

I think I already know that the answer is that this can't be done, but I'll ask anyways. Suppose you want to use an RDBMS to store messages for a threaded message forum like usenet and then display the messages. A toy table definition (that I've tried to make standards compliant) might look like: create table messages ( message_id...

I can't seem to get the file configured correctly. This works: montgomery@spudbox1: psql mydb This does NOT work: montgomery@spudbox1:> psql -h 10.10.10.70 mydb psql: FATAL 1: No pg_hba.conf entry for host 10.10.10.70, user montgomery, database mydb

I have 90% of my function working and I know to get the next 10% it is justa matter of getting the quotations and the escaping quotations correct. Here is the portion that does work: <working code> -- Function: public.get_factory_ytd() CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record AS' declare ytd_record record;...

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),

Hello, I'm in the middle of the migration process from MySQL to PostgreSQL and I cannot understand why this query isn't working (in MySQL it's working fine). PG returns: ERROR: Relation "_con" does not exist This is my query: SELECT _CON.con_id, _MOD.mod_ty,

Hi All, I've a problem with unclosed connections. Once a client aborts a connection accidentelly (client crash or power failure eg.), it stucks in and postgres won't restart or stop. Is there any way to close unused (dead) connections. I'd guess that some kind of connection timeout option should do this. Aren't I right? Tank you in...

I've found a number of basic references for PL/PGSQL, but am looking for something more complete. First question, is there available a *complete* reference for PL/PGSQL? I'm using PostgreSQL version 7.2.3-5.73 (for Redhat rpm packaging version), so it is version 7.2 I am concerned with information on (this is a default version, so at least on...

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...

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...

Has some one come up with a similar type script that could be used in a Postgresql database? The script below was created for a SQLServer database. Thx, -Martin ++++++++++++++++++++++++++++++++++++++ http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci913717,00.html In the early stages of application design DBA or a developer...

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...

Why does '*select count(id) from "tblContacts"'* do a sequential scan when the field '*id*' is indexed using a btree? MySql simply looks at the index which is keeping a handy record of the number of rows. Can anybody explain how and why postgres does this query like it does? Many thanks

We are using Postgresql as the backend database for our upcoming software application. We would like Postgresql to have the following functionalities : 1. Standard Error Codes along with textual error messages including error codes for errors while dumping/restoring database/s. 2. Native Windows Support. The above features are believed to...

Hello, There is now an updated version of plPerl with trigger support available at the Command Prompt community page. It can be viewed with documentation here: https://www.commandprompt.com/entry.lxp?lxpe=285 Sincerely, Joshua Drake

Greetings All, Running pg 7.3.4 and was reading: http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00018.php . Basically want to assign values to an array and then a 2d array. However I can't get this to run in properly I get a syntax error (at or near ", output_txt_arr TEXT, output_str text ); CREATE OR REPLACE FUNCTION...

hi, I am becoming more and more convinced that in order to achieve the required performance and scalability I need to split my data amoungst many backend machines. Ideally I would start with about 10 machine and have 1/10th of the data on each. As the data set grows I would then buy additional machines to spread the data over.

Is there a way to reset the xlog on postgres 7.1 ? Due to complete & intensive braindamage of sysadmin (=me) the whole pg_xlog-folder has been emptied on a old 7.1-installation. pg_resetxlog is not part of 7.1-installation and I wonder if there is any other way to fire postgres up again. (To make my day complete : there arent backups...

Hello, I need to get a float8 to display as a number db=# SELECT CAST('0.00001' AS float8); float8 -------- 1e-05 (1 row)

Greetings, I am new to this list. I have a problem with BLOBS using PostgreSQL7.3.4. I used the lo_export utility to insert an image into a trial database. I have not been able to use the lo_export utility to retrieve it. I have done this successfully before using PostgreSQL7.2. Assuming I have a table name TABLE with columns named ...

On my RH 9.0 linux system when ./configuring 7.3.4 I get an error about not finding the readline libs, however both the readline and readline-devel rpms are installed: # rpm -qa | grep readline readline-4.3-5 readline-devel-4.3-5 What library/rpm am I missing?

HI, let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id) Let's say a transaction can have multiple types: TYPE1, TYPE2 for example. EACH type has his own definition and his own table. Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a...

Hi, SELECT 'abc'::text || 'def'::text; returns 'abcdef' as we know. SELECT 'abc'::text || ''::text; returns 'abc'

Well folks, we are now coming into the home stretch of another long development cycle, and its time for more vigorous and extensive testing .... Last night, we bundled up Beta4, which is the first one that we've officially announced "publicly", with the other 3 having been only announced on -hackers ... This release, depending on the bug...