[Home]

Summary:ASTERISK-14919: [patch] Schema problem with res_pgsql
Reporter:Maciej Krajewski (jamicque)Labels:
Date Opened:2009-10-01 06:37:17Date Closed:2009-10-06 14:44:46
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Resources/res_config_pgsql
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) 20091002__issue16000__1.6.1.diff.txt
( 1) 20091002__issue16000.diff.txt
( 2) startup.res
Description:When you are using reltime with pgsql, asterisk on every connect check the table and it's columns.
For example it's made by querry (for meetme):

SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc, a.atttypmod FROM pg_class c, pg_type t, pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.atthasdef AND d.adrelid = a.attrelid AND d.adnum = a.attnum WHERE c.oid = a.attrelid AND a.atttypid = t.oid AND (a.attnum > 0) AND c.relname = 'meetme' ORDER BY c.relname, attnum

The problem is that it does not check the schema. If you have two different schemas and in every one table meetme, in result Asterisk will try to insert double statements into one table, which will result in an error.

Comments:By: Maciej Krajewski (jamicque) 2009-10-01 08:41:55

This in not a DUPLICATE!
ID ASTERISK-1581001  concerns cdr_pgsql
and ASTERISK-1581000 res_config_pgsql
By: Leif Madsen (lmadsen) 2009-10-01 08:46:26

How are they not duplicate? I imagine the same underlying function is responsible for the issue.
By: Maciej Krajewski (jamicque) 2009-10-01 08:51:29

actually the select in above functions slightly differ (but both gives a bed result). Is there anything else what I can do to help solving this problem?
By: Leif Madsen (lmadsen) 2009-10-01 08:59:16

Setting this to Resources/res_config_pgsql since that is the closest I can find for a category. Per Tilghman on IRC, this is an issue with res_pgsql.so.
By: Leif Madsen (lmadsen) 2009-10-01 09:05:13

<leifmadsen> Corydon76-dig: any additional information I should request from the reporter?
<Corydon76-dig> Nope, I know what the problem is
<Corydon76-dig> I just need to spend the several hours extracting the new SQL from the psqlodbc driver
<Corydon76-dig> probably varying the SQL, based upon server version, same as they're doing there
By: Tilghman Lesher (tilghman) 2009-10-01 12:39:58

Once this is verified to work, I can quickly port the changes over to the CDR driver, as well.
By: Maciej Krajewski (jamicque) 2009-10-01 16:50:57

i was patching the file from 1.6.1.6, and here is the output:

wget 'https://issues.asterisk.org/file_download.php?file_id=24037&type=bug' -O - | patch -p0


patching file res/res_config_pgsql.c
Hunk #1 FAILED at 50.
Hunk #2 succeeded at 101 (offset -13 lines).
Hunk #3 succeeded at 113 with fuzz 2 (offset -13 lines).
Hunk #4 FAILED at 122.
Hunk ASTERISK-1 FAILED at 195.
Hunk ASTERISK-2 succeeded at 214 (offset -13 lines).
Hunk ASTERISK-3 succeeded at 1353 (offset -117 lines).


I have a question, will your changes update all the tables in different schemas during the insert, or only one?
In my opinion it should do the querry on only one table. (which you should define in config).
What would happen if you have two schemas with tables let's say meetme, where on would be the proper one for asterisk and the second will be a different table (not for asterisk usage, with different structure)?


By: Tilghman Lesher (tilghman) 2009-10-01 17:33:12

Patch updated.  Apparently, 1.6.1 varies slightly from trunk.

No, the changes should make it such that only one table is queried.  If you want to specify a schema other than the connection default, you should specify it by concatenating the schema name and a period and prefixing that to the tablename.  Otherwise, it will assume the current schema only.
By: Maciej Krajewski (jamicque) 2009-10-01 17:47:05

so far so good,
tested, works like charm.

Waiting for cdr...
By: Tilghman Lesher (tilghman) 2009-10-02 14:19:08

New patch uploaded that hits both modules.
By: Maciej Krajewski (jamicque) 2009-10-02 16:54:47

res_pgsql works fine,
but cdr has below errors.

[Oct  2 23:51:00] ERROR[24576]: cdr_pgsql.c:311 pgsql_log: Connection may have been lost... attempting to reconnect.
[Oct  2 23:51:00] ERROR[24576]: cdr_pgsql.c:314 pgsql_log: Connection reestablished.
[Oct  2 23:51:00] ERROR[24576]: cdr_pgsql.c:320 pgsql_log: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Oct  2 23:51:00] ERROR[24576]: cdr_pgsql.c:321 pgsql_log: Reason: ERROR:  column "calldate" specified more than once
LINE 1: INSERT INTO cdr ("calldate","calldate","clid","clid","src","...
By: Tilghman Lesher (tilghman) 2009-10-03 09:48:54

Please run a trace, grab the query it runs at boot/reload, and run that query independently against your database and paste both the query and the result.
By: Maciej Krajewski (jamicque) 2009-10-03 16:27:42

Sorry tilghman, it seems it works fine. It was my mistake, I recompile the asterisk yesterday and made the restart, and it was working as reported.

Today I've made the clean install of 1.6.1.6 and works fine (maybe the changed source didn't recompile) I don't now. Once again sorry for misunderstanding.

I've tested it today, made a couple of restarts and everything works fine.
Thanks.
By: Maciej Krajewski (jamicque) 2009-10-03 16:28:38

I report the thing you wanted:
query on start:
SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc, a.atttypmod FROM (((pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND c.relname = 'cdr' AND n.nspname = current_schema()) INNER JOIN pg_catalog.pg_attribute a ON (NOT a.attisdropped) AND a.attnum > 0 AND a.attrelid = c.oid) INNER JOIN pg_catalog.pg_type t ON t.oid = a.atttypid) LEFT OUTER JOIN pg_attrdef d ON a.atthasdef AND d.adrelid = a.attrelid AND d.adnum = a.attnum ORDER BY n.nspname, c.relname, attnum

result:
in file startup.res

I think the ticket can be closed.


By: Digium Subversion (svnbot) 2009-10-06 14:34:53

Repository: asterisk
Revision: 222309

U   trunk/cdr/cdr_pgsql.c
U   trunk/res/res_config_pgsql.c

------------------------------------------------------------------------
r222309 | tilghman | 2009-10-06 14:34:53 -0500 (Tue, 06 Oct 2009) | 10 lines

Change schema query to involve the use of an optional schema parameter.
This change is done in such a way as to allow the driver to continue to
function with older databases which don't have these features.
(closes issue ASTERISK-14919)
Reported by: jamicque
Patches:
      20091002__issue16000.diff.txt uploaded by tilghman (license 14)
      20091002__issue16000__1.6.1.diff.txt uploaded by tilghman (license 14)
Tested by: jamicque

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=222309
By: Digium Subversion (svnbot) 2009-10-06 14:38:02

Repository: asterisk
Revision: 222310

_U  branches/1.6.1/
U   branches/1.6.1/cdr/cdr_pgsql.c
U   branches/1.6.1/res/res_config_pgsql.c

------------------------------------------------------------------------
r222310 | tilghman | 2009-10-06 14:38:02 -0500 (Tue, 06 Oct 2009) | 17 lines

Recorded merge of revisions 222309 via svnmerge from
https://origsvn.digium.com/svn/asterisk/trunk

........
 r222309 | tilghman | 2009-10-06 14:31:39 -0500 (Tue, 06 Oct 2009) | 10 lines
 
 Change schema query to involve the use of an optional schema parameter.
 This change is done in such a way as to allow the driver to continue to
 function with older databases which don't have these features.
 (closes issue ASTERISK-14919)
  Reported by: jamicque
  Patches:
        20091002__issue16000.diff.txt uploaded by tilghman (license 14)
        20091002__issue16000__1.6.1.diff.txt uploaded by tilghman (license 14)
  Tested by: jamicque
........

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=222310
By: Digium Subversion (svnbot) 2009-10-06 14:44:46

Repository: asterisk
Revision: 222311

_U  branches/1.6.2/
U   branches/1.6.2/cdr/cdr_pgsql.c
U   branches/1.6.2/res/res_config_pgsql.c

------------------------------------------------------------------------
r222311 | tilghman | 2009-10-06 14:44:45 -0500 (Tue, 06 Oct 2009) | 17 lines

Merged revisions 222309 via svnmerge from
https://origsvn.digium.com/svn/asterisk/trunk

........
 r222309 | tilghman | 2009-10-06 14:31:39 -0500 (Tue, 06 Oct 2009) | 10 lines
 
 Change schema query to involve the use of an optional schema parameter.
 This change is done in such a way as to allow the driver to continue to
 function with older databases which don't have these features.
 (closes issue ASTERISK-14919)
  Reported by: jamicque
  Patches:
        20091002__issue16000.diff.txt uploaded by tilghman (license 14)
        20091002__issue16000__1.6.1.diff.txt uploaded by tilghman (license 14)
  Tested by: jamicque
........

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=222311