[GENERAL] Difference between inet and cidr

May I know what is the difference among cidr and inet? I read through Network Address Type (http://www.postgresql.org/docs/8.3/static/datatype-net-types.html) """The essential difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, wh

[GENERAL] Drop CHECK Constraint

drop the above CHECK constraint, I will do ALTER TABLE backup_table DROP CONSTRAINT backup_table_fk_lot_id_check; (I assume the constraint name will be backup_table_fk_lot_id_check) Is there any more robust way? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list

Re: [GENERAL] Duplicated tables of certain columns

ot_id status value 1 99 1 100 2 99 1 101 Thanks and Regards Yan Cheng CHEOK --- On Mon, 2/21/11, Yan Cheng CHEOK wrote: > From: Yan Cheng CHEOK > Subject: Duplicated tables of certain columns > To: pgsql-general@

[GENERAL] Duplicated tables of certain columns

unit_id [PK]fk_lot_id status value 1 99 1 100 2 99 1 101 May I know how I can achieve these by using combination of SQL command? Thanks! Thanks and Regards Yan Cheng CHEOK -- Se

[GENERAL] Fast Insert and Update (through COPY)

other techniques. Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] C++ code - PGRES_TUPLES_OK is not returned when DELETE performed

ECT statement, PGRES_TUPLES_OK can be returned. Thanks and Regards Yan Cheng CHEOK -- 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] Slow connection once the PC is network connected

Thanks. That's work pretty well. --- On Tue, 10/26/10, Alban Hertroys wrote: > From: Alban Hertroys > Subject: Re: [GENERAL] Slow connection once the PC is network connected > To: "Yan Cheng CHEOK" > Cc: pgsql-general@postgresql.org > Date: Tuesday, October 26,

[GENERAL] Slow connection once the PC is network connected

overcome this? I connect the database using C++ code PQconnectdb("dbname = ProductionFactory user = postgres password = password"); Thanks. Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your su

[GENERAL] How to insert wchar_t type string to PostgreSQL

t find any function as follow in libpq library. PGresult *PQexec(PGconn *conn, const wchar_t *query); May I know, how I can store wchar_t * typed string into PostgreSQl, using C library? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgres

[GENERAL] default ordering of query result - are they always guarantee

egards Yan Cheng CHEOK -- 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] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

The pgadmin result seems different with my machine. My friend and I are using Windows machine. Are you using Linux machine? Thanks and Regards Yan Cheng CHEOK --- On Mon, 5/17/10, Guillaume Lelarge wrote: > From: Guillaume Lelarge > Subject: Re: [GENERAL] Why Performance of SQL Qu

[GENERAL] Why Performance of SQL Query is *much* Slower in GUI PgAdmin

un through command prompt instead. It tooks 2 seconds. May I know what is the reason, SQL query execution is *much* slower in GUI PgAdmin? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscrip

[GENERAL] Run Vacuum Through JDBC

at console. I check at server status through pgadmin, also get no hint whether Vacuum operation had been executed. Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.or

[GENERAL] Increasing checkpoint_segments - Any bad thing might happen?

eing written by the 3 threads. The read threads will perform retry for every 200ms, lasted for 4 seconds. I was thinking, what is the consequent of increasing "checkpoint_segments", say, from 3 to 6. Any bad thing might happen? Thanks and Regards Yan Cheng CHEOK --

Re: [GENERAL] Deadlock occur while creating new table to be used in partition.

LOCK command required a dummy table. FUNCTION get_existing_or_create_lot BEGIN LOCK dummy_table ... ... END;$BODY$ Thanks and Regards Yan Cheng CHEOK --- On Mon, 4/26/10, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] Deadlock occur while creating new ta

Re: [GENERAL] Deadlock occur while creating new table to be used in partition.

By the way, how I can diagnostic, what is 1) relation 46757 2) database 46753 Thanks and Regards Yan Cheng CHEOK --- On Mon, 4/26/10, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] Deadlock occur while creating new table to be used in > partition. > To: "Yan C

Re: [GENERAL] Deadlock occur while creating new table to be used in partition.

e (value) WHERE value IS NULL;'; EXECUTE 'CREATE INDEX idx_lot_' || measurement_table_index || ' ON ' || quote_ident(measurement_table_name) || '(lot_id);'; END IF; RETURN QUERY EXECUTE 'SELECT * FROM get_lot_by_id($1)&#x

[GENERAL] Deadlock occur while creating new table to be used in partition.

function "get_existing_or_create_lot" line 39 at EXECUTE statement 2010-04-26 13:28:28 MYTSTATEMENT: SELECT * FROM get_existing_or_create_lot('Testing02') May I know why does deadlock happen? How can I avoid? Thanks and Regards Yan Cheng CHEOK -- 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] unable to start postgresql server services (Windows XP/Professional/Service Pack3)

Oh. Then do you know any way I may recover, without losing any transaction? Thanks and Regards Yan Cheng CHEOK --- On Wed, 4/21/10, Scott Marlowe wrote: > From: Scott Marlowe > Subject: Re: [GENERAL] unable to start postgresql server services (Windows > XP/Professional/Service Pa

Re: [GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)

Using the command solve the problem. Bravo! D:\Program Files\PostgreSQL\8.4\bin>pg_resetxlog -f "D:\Program Files\PostgreSQL\8.4\data" Transaction log reset Thanks and Regards Yan Cheng CHEOK --- On Wed, 4/21/10, Yan Cheng CHEOK wrote: > From: Yan Cheng CHEOK > Sub

Re: [GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)

ted with exit code 3 2010-04-21 13:34:58 MYTLOG: aborting startup due to startup process failure Thanks and Regards Yan Cheng CHEOK --- On Wed, 4/21/10, Yan Cheng CHEOK wrote: > From: Yan Cheng CHEOK > Subject: unable to start postgresql server services (Windows > XP/Professional/

[GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)

r. I disable all the firewall, run an antivirus to scan for malware... not work still. Any suggestion on how I can debug this problem? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Side effect of synchronous_commit = off

not being "flushed" to the table. Hence, process B will read the row x as false? If this situation will happen, is it possible that Process B may issues a command, use to "flush all" pending data to be written to disk? Thanks and Regards Yan Cheng CHEOK -- Sent via pgs

[GENERAL] Alternative to UPDATE (As COPY to INSERT)

1 Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch'], array['LSL','USL','Nominal','Average','StdDev','StdDev3','CPK','Min','Max','Total','Valid','Invalid','Variance','LSL','USL','Nominal','Av erage','Std

[GENERAL] Question on RETURNS TABLE example in PostgreSQL documentation

, quantity * price FROM sales WHERE itemno = p_itemno; END LOOP; But that will be two duplicated SELECT statement. Inefficient, right? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] Getting number of affected row after performing update

statisticType; END IF; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION delete_tables(int) OWNER TO postgres; Of course, I get an error at line : IF @@ROWCOUNT=0 THEN May I know what is the correct PostgreSQL syntax for @@ROWCOUNT after update? Thanks! Thanks and Reg

[GENERAL] Make sure there is no two column with same value - What is the best practice?

Hello, currently, I would like to make sure : (1) When insert a new item, there is no 2nd row with same column value. (2) If there is, I will just directly take the existing row. I was thinking out of 2 approach. May I know which one is the common used best practice? // Shall I make the **entir

Re: [GENERAL] Is it necessary to have index for child table in following case?

PQendcopy(Database::instance().getConnection()); Thanks and Regards Yan Cheng CHEOK --- On Thu, 2/4/10, Joe Conway wrote: > From: Joe Conway > Subject: Re: [GENERAL] Is it necessary to have index for child table in > following case? > To: "Yan Cheng Cheok" > Cc: "Alban

[GENERAL] varchar(n) and text

ing TEXT, will there have any overhead on (1) storage space? (2) query performance? (3) write performance? Shall I just use varchar(10) ? Thanks! Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscript

[GENERAL] Shall I apply normalization in the following case?

. For non-normalization table, I need NOT to join. Right now, I cannot justify why I need to use normalization. I afraid I miss out several important points when I turn into un-normalize solution. Please advice if you realize I miss out several important points. Thanks Yan Cheng CHEOK

Re: [GENERAL] Is it necessary to have index for child table in following case?

> > Are you using INSERT or COPY to insert your data? COPY > tends to be a lot faster than separate INSERTs, especially > if you don't wrap the INSERTs in a transaction block and > COMMIT them in batches. > But I do not use File or Stdio. The data is coming in row by row real-time. I need to in

[GENERAL] Need Advice In Improving Database Disc Usage

or my database? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Is it necessary to have index for child table in following case?

for me to create index foreign_key1_index for child table measurement_1? (1) All my SELECT query is performed on parent table measurement. (2) All my data INSERT code is performed based on child table measurement_1. Thanks! Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-gener

[GENERAL] Is this the warning message I should pay attention on it, during table partition

O ACTION ON DELETE CAS CADE ) INHERITS (unit);" PL/pgSQL function "insert_unit" line 29 at EXECUTE statement NOTICE: merging column "unit_id" with inherited definition Is this the warning message I should take any action on it? If not, how I can suppress it? It is qu

[GENERAL] Use Trigger to Remove Table itself when there is no row after delete

May I know how I can use trigger technique, to remove the table itself, when after delete operation, there is 0 row in the table? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Problem after installing triggering function

To reproduce the problem, here is some simple steps to follow : (1) create database named "tutorial" (2) perform the following SQL query : CREATE TABLE impressions_by_day ( advertiser_id SERIAL NOT NULL, day DATE NOT NULL DEFAULT CURRENT_DATE, impressions INTEGER NOT NULL

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Sorry. I didn't get all your points. "defining a primary key constraint implicitly creates an index" - Yup. I agree on this. But what is the purpose, for author to explicitly define index for day? CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (day); Isn't the

Re: [GENERAL] Problem after installing triggering function

Sorry. Some correction. Change But... I am not implementing table partition to But... I am *now* implementing table partition Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/28/10, Yan Cheng Cheok wrote: > From: Yan Cheng Cheok > Subject: Re: [GENERAL] Problem after inst

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/28/10, Alban Hertroys wrote: > From: Alban Hertroys > Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table > Partition > To: "Yan Cheng Cheok" > Cc: pgsql-general@postgresql.org > Da

Re: [GENERAL] Problem after installing triggering function

Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/28/10, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] Problem after installing triggering function > To: "Yan Cheng Cheok" > Cc: pgsql-general@postgresql.org > Date: Thursday, January 28, 2010, 12:34 AM &g

[GENERAL] Problem after installing triggering function

dent(unit_table_name) || '(unit_id, fk_lot_id, cycle) VALUES (' || NEW.unit_id || ',' || NEW.fk_lot_id || ',' || NEW.cycle || ')'; RETURN NULL; END; Thanks and Regards Yan Cheng CHEOK -- 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] Primary Key Increment Doesn't Seem Correct Under Table Partition

Hello all, I solve my problem using the following. It seems that when inherit from parent table, the parent table's constraint is not being carried over to child table. CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $BODY$DECLARE measurement_table_index bigint;

Re: [GENERAL] Partitioning on Table with Foreign Key

easurement USING btree (value) WHERE value IS NULL; END IF; Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/21/10, Yan Cheng Cheok wrote: > From: Yan Cheng Cheok > Subject: Re: [GENERAL] Partitioning on Table with Foreign Key > To: "Vick Khera"

[GENERAL] Question on Type of Query Which Will Take Advantage On Table Partition

;2009-01-01' AND day < DATE '2009-03-01' Thanks and Regards Yan Cheng CHEOK -- 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] Primary Key Increment Doesn't Seem Correct Under Table Partition

Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/26/10, Alban Hertroys wrote: > From: Alban Hertroys > Subject: Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table > Partition > To: "Yan Cheng Cheok" > Cc: pgsql-general@postgresql.org > D

[GENERAL] Correct Concept On Table Partition

2,000,000) after partition === we will just need to access table measurement_2 only, which is smaller, and shall be faster. (4) extensive join operation will be involve. I am more concern into read speed. Is this the correct expectation, on table partition? Thanks and Regards Yan C

[GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

) execute script in table-partition.sql (3) SELECT * FROM create_lot(); (4) View on measurement table. Also, is it necessary to create index for measurement_id found in measurement's child table? I am concern on the read speed. Thanks and Regards Yan Cheng CHEOK -- Sent via pg

Re: [GENERAL] Shall I convert all my stored procedure to use "EXECUTE", to ensure I get index-scan

Does it mean, if it isn't broken, don't fix it? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Create Trigger Function For Table Partition.

s not being recognized within EXECUTE statement. How can I avoid this problem? I cannot have "static SQL", as my table name needed to be dynamic generated. Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Shall I convert all my stored procedure to use "EXECUTE", to ensure I get index-scan

ostgres; Is there any rule of thumb to follow? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Optimization on JOIN

b-sequence join? Instead of I first join into a very large table, only I perform filtering (which I assume will be slower) Thanks Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://ww

Re: [GENERAL] Extremely Slow Cascade Delete Operation

Hi Craig Ringer, Really appreciate a lot for your advice! This at least has cleared my doubt, which had been confused me for quite some time. Thanks and Regards Yan Cheng CHEOK --- On Fri, 1/22/10, Craig Ringer wrote: > From: Craig Ringer > Subject: Re: [GENERAL] Extremely Slow C

Re: [GENERAL] Extremely Slow Cascade Delete Operation

other database (like MySQL) experience same issues too? Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/13/10, Grzegorz Jaśkiewicz wrote: > From: Grzegorz Jaśkiewicz > Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation > To: "Yan Cheng Cheok" > Cc: pgsql-

[GENERAL] void stored procedure does return something?

nt ==== But isn't my stored procedure is void? Isn't it shouldn't return anything? Thanks and Regards Yan Cheng CHEOK -- 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] Partitioning on Table with Foreign Key

Make sense to me. Thanks for the advice. I will try that out. Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/21/10, Vick Khera wrote: > From: Vick Khera > Subject: Re: [GENERAL] Partitioning on Table with Foreign Key > To: "Yan Cheng Cheok" > Cc: pgsql-general

[GENERAL] Partitioning on Table with Foreign Key

Opps! But measurement do not have date column. How I can refer to measurement's lot's date? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Can I use LIKE to achieve the following result

Regards Yan Cheng CHEOK -- 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] Why Stored Procedure is Slower In The Following Case?

pe_id = measurement_type_id AND lot_id = $1 AND measurement_type.value LIKE $2' USING _lotID, _type; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 1000; ALTER FUNCTION get_measurements(bigint) OWNER TO postgres; Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/20/10, Ya

[GENERAL] Why Stored Procedure is Slower In The Following Case?

tement is the same. Except stored procedure is having additional 'QUERY'. Does that make the speed difference? Thanks and Regards Yan Cheng CHEOK p/s I have index on measurement_type table using : CREATE INDEX idx_measurement_type_value ON measurement_type USING btree (value)

Re: [GENERAL] SETOF Record Problem

Thanks. I solved my problem using : RETURN QUERY SELECT ... Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SETOF Record Problem

By referring to http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS, section 34.4.8. SQL Functions Returning TABLE I create the following stored procedures. -- Function: get_measurements(bigint) -- DROP FUNCTION get_measurements(bigint); CREATE OR REPLACE FU

[GENERAL] Memory Access Violation While Using PQexec

I encounter case when I call a stored procedure for 299166 th times (intensive, i put a non-stop while true loop to call stored procedure) , the following exception will be thrown from PQexec. I am rather sure the exception are from PQexec, as there is a just before cout and just after cout wra

Re: [GENERAL] Extremely Slow Cascade Delete Operation

lot_id = 3; It takes TWO hours and never able to return! I even run VACUUM, with options FULL + FREEZE + ANALYZE but it does not help at all. Is there other optimization steps I had missed out? Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/13/10, Craig Ringer wrote: > From: Craig Rin

Re: [GENERAL] Extremely Slow Cascade Delete Operation

OMG, I never know what is index (Sorry for my newbies) I will study about them and update you all about their performance. Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/13/10, Craig Ringer wrote: > From: Craig Ringer > Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operatio

Re: [GENERAL] Extremely Slow Cascade Delete Operation

come it takes so long? There are only 1000++ row of unit, where their lot_id is 2. Seems not reasonable to me. :( Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/13/10, Grzegorz Jaśkiewicz wrote: > From: Grzegorz Jaśkiewicz > Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operatio

Re: [GENERAL] Extremely Slow Cascade Delete Operation

It looks like this : http://sites.google.com/site/yanchengcheok/Home/log.txt I put it in google site, for easy reading) Any hint? Thanks! Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/13/10, Grzegorz Jaśkiewicz wrote: > From: Grzegorz Jaśkiewicz > Subject: Re: [GENERAL] Extremel

[GENERAL] Is It Good Practice That I use TableName-Month-Year Convention

ect way, to dynamically generate table name : http://www.postgresql.org/docs/8.1/interactive/ecpg-dynamic.html (2) Is this consider a good approach, to overcome speed problem (especially read speed). Any potential problem I should put an eye on, before I implement this strategy? Thanks and Re

[GENERAL] Extremely Slow Cascade Delete Operation

t_id) REFERENCES unit (unit_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE ); END IF; Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] What is Cost 100 in stored procedure

May I know what is the meaning of Cost 100, at the end of stored procedure? END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION create_tables() OWNER TO postgres; Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgs

[GENERAL] Change Hard Disc Drive

Currently, I am installing PostgreSQL in C:\Program Files\PostgreSQL, with all default settings during installation. I realize my D drive has a much larger space and I would like PostgreSQL to store all the databases in D drive. How can I do so through pgAdmin? Thanks and Regards Yan Cheng

Re: [GENERAL] Best Practice when Encounter Invalid Stored Procedure Parameters

Very nice. Thanks! Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/12/10, Pavel Stehule wrote: > From: Pavel Stehule > Subject: Re: [GENERAL] Best Practice when Encounter Invalid Stored Procedure > Parameters > To: "Yan Cheng Cheok" > Cc: pgsql-general@postg

[GENERAL] Best Practice when Encounter Invalid Stored Procedure Parameters

asurement_values, 1) != array_upper(_measurement_units, 1) THEN RAISE NOTICE 'What I should do here to return early???'; END IF; May I know what is the good practice to handle invalid parameters? I am using libpq to interface with PostgreSQL. Thanks and Regards Yan Cheng CHEOK

[GENERAL] Custom Field for a table row returned from stored procedure

100; ALTER FUNCTION create_lot(text) OWNER TO postgres; I expect the table returns two field for a single row. 1 is configuration_file, 2nd is timestamp, 3rd is timestampex. However, I only able to retrieve configuration_file and timestamp. How can I obtain timestampex too? Thanks and Reg

Re: [GENERAL] Get Unix timestamp from SQL timestamp through libpq

Not working. strftime is use to convert date and time to a string. Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/11/10, Vincenzo Romano wrote: > From: Vincenzo Romano > Subject: Re: [GENERAL] Get Unix timestamp from SQL timestamp through libpq > To: "Yan Cheng Cheok"

Re: [GENERAL] Get Unix timestamp from SQL timestamp through libpq

I try already. Neither of them can accept string parameter, and convert them to unit timestamp (long). Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/11/10, Vincenzo Romano wrote: > From: Vincenzo Romano > Subject: Re: Get Unix timestamp from SQL timestamp through libpq > To: &

[GENERAL] Get Unix timestamp from SQL timestamp through libpq

c/c++ function call, without involving stored procedure. Any suggestion? Thanks! Thanks and Regards Yan Cheng CHEOK -- 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] Return Single Row Result After Inserting (Stored Procedure)

, operator_name, machine_name) VALUES(configurationFile, operatorName, machineName) RETURNING * INTO _lot; return _lot; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres; Thanks and Regards Yan Cheng CHEOK --- On Mo

Re: [GENERAL] Return Single Row Result After Inserting (Stored Procedure)

ement Any suggestion? Thanks! Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Return Single Row Result After Inserting (Stored Procedure)

_name) VALUES(configurationFile, operatorName, machineName); END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION create_lot(text, text, text) OWNER TO postgres; Thanks and Regards Yan Cheng CHEOK p/s May I know what is the purpose of "COST 100"?

[GENERAL] check the execution status of stored procedure

ype of stored procedure is void, I will always fall in to the block if (PQresultStatus(res) != PGRES_COMMAND_OK) {} Is there any better way, I can check whether the stored procedure had been executed without problem? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailin

[GENERAL] create table if does not exists

CREATE TABLE MYTABLE ( "value" text NOT NULL ); end if; When I run for the 2nd time, 'table not there yet.' still being printed out, al though I check through pgadmin, the MYTABLE is there. May I know how I can fix this? Thanks and Regards Yan Cheng CHEOK -- Se

[GENERAL] Use C++ to iterate integer array returned from stored procedure

3), Here is my stored procedure : CREATE OR REPLACE FUNCTION get_array_test() RETURNS integer[] AS DECLARE i int4[]; BEGIN i[1] = 100; i[2] = 200; i[3] = 300; return i; END; Is there any way, I can obtain the stored procedure result in c/c++ int array, instead of 1 line of ch

Re: [GENERAL] PostgreSQL Write Performance

ing on Timing is on. SemiconductorInspection=# ; Time: 0.660 ms SemiconductorInspection=# ; Time: 0.517 ms SemiconductorInspection=# ; Time: 2.249 ms SemiconductorInspection=# I assume there shall be no hard disc activity involved, as I am sending "empty" SQL statement over. Thanks and Reg

[GENERAL] Is there any different for foreign key to be serial instead of integer

I came across a lot of similar example for foreign key CREATE TABLE orderinfo ( orderinfo_id serial , customer_id integer NOT NULL, date_placed date NOT NULL, date_shipped date , shipping numeric(7,2) , CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id), CONSTRAINT orderinfo_customer_id_fk FOREIGN

[GENERAL] Optimized Select Statement

thout using stored procedure, how can I send a SQL statement to PostgreSQL, to tell it that I need only 1 measurement_type_id, to speed up the SELECT speed. Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs

Re: [GENERAL] PostgreSQL Write Performance

improve so that PostgreSQL write performance is near to file? If not, I need to plan another strategy, to migrate my flat text file system, into PostgreSQL system smoothly. Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/5/10, Craig Ringer wrote: > From: Craig Ringer > Subje

Re: [GENERAL] PostgreSQL Write Performance

Thanks for the information. I wrote a plan c program to test the performance. Its time measurement is very MUCH different from pgAdmin. Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/6/10, Andres Freund wrote: > From: Andres Freund > Subject: Re: [GENERAL] PostgreSQL Write Perfo

Re: [GENERAL] PostgreSQL Write Performance

t file.. However, using flat file is quite a mess, when come to generating reports to customers. Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/5/10, Dann Corbit wrote: > From: Dann Corbit > Subject: Re: [GENERAL] PostgreSQL Write Performance > To: "Yan Cheng Cheok&q

Re: [GENERAL] PostgreSQL Write Performance

write operation when the pending statement had reached 1000" Thanks and Regards Yan Cheng CHEOK --- On Tue, 1/5/10, Scott Marlowe wrote: > From: Scott Marlowe > Subject: Re: [GENERAL] PostgreSQL Write Performance > To: "Yan Cheng Cheok" > Cc: pgsql-general@postgresql.org

[GENERAL] PostgreSQL Write Performance

right corner) Am I doing the correct way to benchmarking? I am not sure whether this is expected performance? For me, I am expecting the time measurement is in nano seconds :p Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key

Can you please provide me an example of a stored procedures to achieve that? Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/4/10, Alban Hertroys wrote: > From: Alban Hertroys > Subject: Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key > To: "Yan Cheng Cheok"

[GENERAL] Shall I use PostgreSQL Array Type in The Following Case

I realize there is Array data type for PostgreSQL. http://www.postgresql.org/docs/8.1/interactive/arrays.html Currently, I need to use database to store measurement result of a semiconductor factory. They are producing semicondutor units. Every semicondutor units can have variable number of me

Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key

>From general point of view, having 3 SQL statement wrapped in a single stored >procedure shall perform better due to reduced overhead to communicate with SQL >server. Is that true? Or that is my false assumption? Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/4/10, Alban Hertro

[GENERAL] Insert Data Into Tables Linked by Foreign Key

d for this simple operation!!! Is there any better way, which can be achievable using 1 SQL statement? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general