On Tuesday, June 11, 2019 at 10:26:59 AM UTC-7, Ryan Kingston wrote:
>
>
> If I pass an invalid query to the dataset.empty? method, it raises a
> Sequel::DataseDisconnectError. It would be a lot more helpful if it raised
> a Sequel::DatabaseError like it does if I call dataset.all
>
>
> EXISTS_ANYWHERE_SQL = <<~SQL.freeze
> SELECT 1
> FROM ap_invoices
> WHERE
> vendor_no = :vendor_no AND
> REPLACE(invoice_no, ' ', '') = REPLACE(:invoice_no, ' ', '')
> SQL
> dataset = DB.fetch(EXISTS_ANYWHERE_SQL, vendor_no: 1, invoice_no: 1)
> dataset.empty?
> dataset.all
>
>
>
> dataset.empty? tells me
> Sequel::DatabaseDisconnectError: PG::ConnectionBad: PQconsumeInput()
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> dataset.all tells me
> (0.006573s) SET standard_conforming_strings = ON
> (0.007248s) SET client_min_messages = 'WARNING'
> (0.005128s) SET DateStyle = 'ISO'
> PG::UndefinedFunction: ERROR: function replace(bigint, unknown, unknown)
> does not exist
> LINE 5: REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', ...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.: SELECT 1
> FROM ap_invoices
> WHERE
> vendor_no = 1AND
> REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
>
> Sequel::DatabaseError: PG::UndefinedFunction: ERROR: function replace(
> bigint, unknown, unknown) does not exist
> LINE 5: REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', ...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> The error message from dataset.all is a lot more helpful than the error
> message from dataset.empty? Would it be possible for dataset.empty? to
> raise a DatabaseDisconnectError to show what is wrong with the query?
>
This is really a driver (ruby-pg/libpq) issue, not an issue with Sequel. In
your case, when you call dataset.empty?, libpq shows the server closed the
connection, so ruby-pg raises PG::ConnectionBad, which Sequel transforms
as Sequel::DatabaseDisconnectError. For dataset.all, the server just
reports the problem with the query without closing the connection, ruby-pg
raises PG::UndefinedFunction, which Sequel transforms to
Sequel::DatabaseError.
I tried your example and can't reproduce the issue on PostgreSQL 11.3 with
ruby-pg 1.1.4. I get a PG::UndefinedFunction error for both dataset.empty?
and dataset.all (after creating an appropriate ap_invoices table).
I, [2019-06-11T11:41:17.975563 #12343] INFO -- : (0.000406s) SET
standard_conforming_strings = ON
I, [2019-06-11T11:41:17.975876 #12343] INFO -- : (0.000103s) SET
client_min_messages = 'WARNING'
I, [2019-06-11T11:41:17.976024 #12343] INFO -- : (0.000092s) SET DateStyle
= 'ISO'
Your database is stored in DB...
irb(main):001:0> EXISTS_ANYWHERE_SQL = <<~SQL.freeze
SELECT 1
FROM ap_invoices
irb(main):002:0" SELECT 1
irb(main):003:0" FROM ap_invoices
irb(main):004:0" WHERE
irb(main):005:0" vendor_no = :vendor_no AND
irb(main):006:0" REPLACE(invoice_no, ' ', '') = REPLACE(:invoice_no,
' ', '')
irb(main):007:0" SQL
=> "SELECT 1\nFROM ap_invoices\nWHERE\n vendor_no = :vendor_no AND\n
REPLACE(invoice_no, ' ', '') = REPLACE(:invoice_no, ' ', '')\n"
irb(main):008:0> dataset = DB.fetch(EXISTS_ANYWHERE_SQL, vendor_no: 1,
invoice_no: 1)
=> #<Sequel::Postgres::Dataset: "SELECT 1\nFROM ap_invoices\nWHERE\n
vendor_no = 1 AND\n REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')\n">
E, [2019-06-11T11:41:45.720895 #12343] ERROR -- : PG::UndefinedFunction:
ERROR: function replace(integer, unknown, unknown) does not exist
LINE 5: REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.: SELECT 1
FROM ap_invoices
WHERE
vendor_no = 1 AND
REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
Traceback (most recent call last):
16: from /data/code/sequel/lib/sequel/adapters/postgres.rb:610:in
`fetch_rows'
15: from /data/code/sequel/lib/sequel/dataset/actions.rb:1088:in
`execute'
14: from /data/code/sequel/lib/sequel/adapters/postgres.rb:314:in
`execute'
13: from /data/code/sequel/lib/sequel/database/connecting.rb:270:in
`synchronize'
12: from
/data/code/sequel/lib/sequel/connection_pool/threaded.rb:92:in `hold'
11: from /data/code/sequel/lib/sequel/adapters/postgres.rb:314:in
`block in execute'
10: from /data/code/sequel/lib/sequel/adapters/postgres.rb:518:in
`check_database_errors'
9: from /data/code/sequel/lib/sequel/adapters/postgres.rb:314:in
`block (2 levels) in execute'
8: from /data/code/sequel/lib/sequel/adapters/postgres.rb:496:in
`_execute'
7: from /data/code/sequel/lib/sequel/adapters/postgres.rb:140:in
`execute'
6: from /data/code/sequel/lib/sequel/adapters/postgres.rb:116:in
`check_disconnect_errors'
5: from /data/code/sequel/lib/sequel/adapters/postgres.rb:140:in
`block in execute'
4: from /data/code/sequel/lib/sequel/adapters/postgres.rb:152:in
`execute_query'
3: from /data/code/sequel/lib/sequel/database/logging.rb:43:in
`log_connection_yield'
2: from /data/code/sequel/lib/sequel/adapters/postgres.rb:152:in
`block in execute_query'
1: from /data/code/sequel/lib/sequel/adapters/postgres.rb:152:in
`async_exec'
Sequel::DatabaseError (PG::UndefinedFunction: ERROR: function
replace(integer, unknown, unknown) does not exist)
LINE 5: REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
E, [2019-06-11T11:41:56.743006 #12343] ERROR -- : PG::UndefinedFunction:
ERROR: function replace(integer, unknown, unknown) does not exist
LINE 5: REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.: SELECT 1
FROM ap_invoices
WHERE
vendor_no = 1 AND
REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
Traceback (most recent call last):
16: from /data/code/sequel/lib/sequel/adapters/postgres.rb:610:in
`fetch_rows'
15: from /data/code/sequel/lib/sequel/dataset/actions.rb:1088:in
`execute'
14: from /data/code/sequel/lib/sequel/adapters/postgres.rb:314:in
`execute'
13: from /data/code/sequel/lib/sequel/database/connecting.rb:270:in
`synchronize'
12: from
/data/code/sequel/lib/sequel/connection_pool/threaded.rb:92:in `hold'
11: from /data/code/sequel/lib/sequel/adapters/postgres.rb:314:in
`block in execute'
10: from /data/code/sequel/lib/sequel/adapters/postgres.rb:518:in
`check_database_errors'
9: from /data/code/sequel/lib/sequel/adapters/postgres.rb:314:in
`block (2 levels) in execute'
8: from /data/code/sequel/lib/sequel/adapters/postgres.rb:496:in
`_execute'
7: from /data/code/sequel/lib/sequel/adapters/postgres.rb:140:in
`execute'
6: from /data/code/sequel/lib/sequel/adapters/postgres.rb:116:in
`check_disconnect_errors'
5: from /data/code/sequel/lib/sequel/adapters/postgres.rb:140:in
`block in execute'
4: from /data/code/sequel/lib/sequel/adapters/postgres.rb:152:in
`execute_query'
3: from /data/code/sequel/lib/sequel/database/logging.rb:43:in
`log_connection_yield'
2: from /data/code/sequel/lib/sequel/adapters/postgres.rb:152:in
`block in execute_query'
1: from /data/code/sequel/lib/sequel/adapters/postgres.rb:152:in
`async_exec'
Sequel::DatabaseError (PG::UndefinedFunction: ERROR: function
replace(integer, unknown, unknown) does not exist)
LINE 5: REPLACE(invoice_no, ' ', '') = REPLACE(1, ' ', '')
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/7f07025e-90a2-4cf9-9990-3135b4d54049%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
